Architecture – Business Layer in Database Logic System

Your DAL should return your data objects

Ideally your DAL should be a "black box" object, that your application code can use to ask for a data object or manipulate existing data objects. Sometimes there is another layer put between the DAL and the application code called the Repository, which further separates the two layers, although this is not always needed.

In addition, you usually do not want your business objects being able to create themselves. This can cause security holes where someone can use your library, and create a new instance of your object by calling .Load(someId) on it, and it merges together two layers that should be completely separate.

I also don't recommend providing a.Load(DataSet ds) method because if the data set definition changes, you'll have to hunt down the data objects which use that data set and change them. It's easier to keep all your data access code in one place, so if you change the data access query, you should only have to change your DAL layer.

Your colleagues are conflating architecture with implementation.

The idea behind a multi-tiered application is simply that it's broken up into parts that encapsulate certain kinds of processing (storage, business logic, presentation) and communicate with each other using well-defined interfaces. Just as it's possible to successfully do things that resemble object-oriented programming in non-object-oriented languages, it's possible to do the same with multiple tiers within one environment, such as a database server. What doing either of those successfully have in common is a need for care, discipline and an understanding of the compromises involved.

Let's look at a three-tiered application where two of the tiers have been implemented on a database:

• Data Tier: Consists of database tables accessed using the four standard table operations (INSERT, UPDATE, DELETE and SELECT).
• Logic Tier: Consists of stored procedures that implement only business logic and access the data tier using only the methods outlined above.
• Presentation Tier: Consists of a web server running code that accesses the logic tier by making only stored procedure calls.

This is a perfectly-acceptable model, but it comes with some tradeoffs. The business logic is implemented in a way that gives it fast, easy access to the data tier and may allow doing things that would have to be done "the hard way" by a logic tier outside the database. What you give up are the ability to easily move either tier to some other bit of technology and carefree implementation (i.e., you have to be extra careful that the tiers don't use facilities that are available in the database but outside their defined interfaces).

Whether or not this kind of thing and the tradeoffs it brings are acceptable in a given situation is something you and your colleagues have to determine using your judgment.