In my opinion, you should ALWAYS use a BLL (Business Logic Layer) between your web tier and your DAL (Data Access Layer).
I appreciate that for some of the more "simple" queries, the BLL will closely mimic the DAL (e.g. Fetch all countries, Fetch all Product Types etc.), but to honest, even in your example:
(Fetch all customers with surname of
'Atwood')
there is "business logic" being expressed here - A desire for the data records to be filtered by surname, if nothing else!
By implementing a BLL from the start of a project it becomes incredibly easy to insert either validation or additional "logic" as and when the need may arise (and if your project is a commercial application, that need will almost certainly arise eventually if it isn't there at the beginning of the project). Adding in additional logic such as:
Fetch all customers who have spent
over $10000 this year
or
Don't allow customers with the surname of 'Atwood'
to purchase items over $1000
becomes significantly easier when a true BLL is involved, rather than trying to crowbar this logic into the web tier.
Bear in mind that with the kinds of queries above, we're almost certainly talking about multiple entities and database tables that will have to join together with specifically defined relationships in order to implement this functionality. Trying to achieve this by directly manipulating the DAL becomes messy since you'll be dealing with multiple entities and classes. A BLL here would greatly simplify your web tier code, since the BLL will encapsulate those entity relationships behind a greatly simplified interface.
This "separation of concerns" becomes increasing important when and if the need to change the user interface arises.
On at least two separate occasions now, I've worked on commercial web applications with a web site user interface, and have been eventually asked (due to business need arising from clients seeking greater integration within their software products) to produce a web service interface offering the exact same functionality as the web site.
Had I embedded any business logic within my web tier, I would have had to duplicate and re-write that logic when implementing my web service. As it was, I ensured that all business logic was encapsulated within BLL classes, which meant that I simply had to design a series of web service interface method calls, and plug these up against calls to methods on the BLL classes (I actually used the Facade Design Pattern in places to simplify the web service API).
In all, I can think of no reason to NOT include a BLL layer between my DAL and my web tier.
At it's easiest, when the BLL closely "mimics" the DAL, yes, there does appear to be a duplication of code and functionality, however, whilst being a little more typing, this also makes it relatively easy to implement.
When it's more involved (such as when significant business logic exists from the very beginning), the separation of concerns helps to reduce repetition (the DRY principle) whilst at the same time significantly simplifying future and ongoing maintenance.
Of course, this assumes you're doing all this "by hand". If you so desire, you can significantly simplify the DAL/BLL/UI layers by utilizing an ORM of which there are many!
(i.e. LINQ-to-SQL/Entities, SubSonic, NHibernate etc.)
The way the I do this is the BO expects a DataReader or a DataContext or whatever back from the DAL, not the actual formed object. It is then the job of the BO layer to take and fill itself from the object that came back. The DAL isn't returning back a completed BO. The main thing to remember is that changing something in the BO layer shouldn't cause issues for the DAL layer, but changing something in the DAL layer could cause issues for the BO layer.
A short example of what I typically do
In the BO layer
FillData(){
DataReader dr = DataLayer.GetData("SomePropertyForAStoreProcedure");
If dr.Read(){
Property1 = dr.GetValue("Property1");
//So on and so forth
}
}
In the DAL
DataReader GetData(String SPProperty){
}
Best Answer
Look up a concept called 'Domain Driven Design' - the biggest thing there is using what's called a repository pattern (such as your UserDB class) as an adapter to the database, as well as a factory. Your business objects, or domain objects, then incorporate business logic into themselves and can handle interactions with other business objects.
What technology are you using? Something like ActiveRecord can probably help you a lot.