There is no "best small or mid-size application architecture" as a silver bullet to fit any project, so drop that idea right now or you'll be in for a world of pain down the road.
The architecture for any given project will fit the purpose of that project. In some cases, ASP.NET WebForms with a direct queries into the database will be the most appropriate architecture, in some cases MVC will be the right architecture, in some cases a windows forms application built on top of a web service that connects to a relational database through an ORM like LINQ-to-SQL or NHibernate.
You can't decide on a one-architecture-fits-all approach, it just doesn't work. Each architecture has its merits and weaknesses and thus projects for which it is well suited and projects for which it should be avoided. You should pick the approach that makes the most sense for the current project/scenario.
Given that however, I tend to take a fairly uniform approach.
If I need a quick utility project that does a very specific thing and is highly unlikely to be needed for anything else, I might use a console application with queries against my database hardcoded.
If I need a common set of queries that I'm likely to need from multiple projects, I'll write them as stored procedures to get the performance benefits and build a data access layer that will leverage these stored procedures to give me standardized business objects, in a standard DAL (data access layer)/BOL (business object layer)/BLL (business logic layer) approach. This is advantageous because it means that once I've got this set of libraries built I can float any application over the top - for instance a webforms or MVC application.
MVC is advantageous because of separation of concerns - your controller can interact with your business library simply to access the data it needs and your views are really just that - a view of the data that the user can interact with. The views do nothing more than take the current data view to the user and transport any data changes back from the user to the controller - no logic is held in the view and as such it means that it's far easier to unit test and make changes to components without affecting the rest of the application.
The drawback to a multi-tiered or multi-layered approach like this though is that it takes time to architect it properly and if you're only after a throw-away utility application like they demonstrate on stage at developer conferences then this is complete overkill and I wouldn't bother with it.
Think of it like this: Every layer, every library, every component requires justification. If there is less justification for than against, then don't do it. The key is not to do something without reason - anything you do is correct providing that you have a well thought out reason for it, and by well thought out, I mean that you've found very good reasons for and against and you've made an educated decision, you've not made a decision based on half thoughts, or worse, no thought at all.