Do you separate SQL from the BL?

Question

Many years ago, when I started (C) we wrote the business logic in one place, and all the SQL statements for that business logic in a different place. (The reason was probably due to the fact each file passed a different build process, sql files with the Oracle builder).
But this prevailed in other places, where there is no need for physical separation.

So: Do you embed the SQL into the the actual business logic classes?
Or
Do you separate it into a Bl class with a relevant DL class?

Answer 1

You need to separate out your BL and DAL to some extent. Some will say that doing it a certain way is correct and all other ways are wrong, but there are a lot of varied situations out there. It's a safe bet to say that there should always be some separation.

For example:

1. You might have a Repository strategy where you business logic doesn't even know a database is involved. This is good. It is appropriate more and more often these days, since frameworks are making this pattern easier and easier to implement.

2. It can be legitimate though to have SQL statements visible in your business classes. If you do though, it should only be for specifying the SQL intention. You should not have repetitive data access code cluttering up your business classes; your connection strings, opening connections, etc. are completely separate. Make some good classes to handle that stuff. Even though it might at first glance look like you're mixing BL and DAL, they will in fact be somewhat separated. A word of warning: your classes can get large, and by combining business work and data properties, there can be a confusingly large amount of class members.

I've traditionally favored #2, because I knew how to do it well (used code generation), had some great DA tools, I like SQL, and I really like self-contained objects that are complete mysteries to the outside world and completely handled every to do with that entity.

However, I'm using #1 now because:

• I'm annoyed at data properties and business properties all mixed together, and want to have smaller classes.
• I'm using an MVC pattern, and I like passing "lighter" data objects to the views
• My framework is making this easier for me (.NET).

Answer 2

In the corner of the Java world I live in we'd separate the persistence logic from the Business Logic. We would tend not to actually write SQL directly, but rather use a persistence mechanism such as JPA.

As an idealised way of thinking we can then consider our business logic separately from the details of the persistence, particular databases and perhaps database schemas.

Even on the occasions when I revert to JDBC and straight SQL I would follow this pattern, keeping all my SQL together to avoid repetition and to isolate the different kinds of logic - BL and Persistence are not the same thing and often need to be thought about separately.

Answer 3

Most of the time to keep things relatively simply, I usually embed the SQL right into the BL layer. If I don't do that I usually create a DL object within any classes used in the business logic objects. This way I have fairly easy access to things I need while keeping them separated to an extent.

Answer 4

I think you will find that most modern architectures (including ORM) require that Data Access concerns such as SQL be totally separate from Business Logic concerns, for maintenance, testing and RDBMS portability reasons.

Some of the ORM solutions still muddy the waters though e.g. should a LINQ2SQL/EF4 DataContext or NHibernate Session be accessible above the data access layer?

FWIW the evolution seems to be along the lines of:

• 2.5 Tier architecture was Business Logic in the database, usually SProcs.
• 3 Tier separates Data and Business concerns, with business concerns typically in code, not DB
• SOA typically adds a service layer over business and data concerns

Personal preference is for a Fowler type Repository pattern as the Data Access Layer, and repositories can be injected into either our Business or Service concerns as needed (Palermo's onion)

HTH

Answer 5

The layers should be clear at least logically, but not all "business logic" should be outside the database.

From the database's point of view, it needs to provide a service of data integrity. If that integrity ensures foreign key constraints, then that low-level domain logic is enshrined in the database and it's a guarantee to your business. Sometimes this may include triggers and stored procedures which ensure that operations are always done or done in a particular order.

So, if the database is guaranteeing something, then this "low-level business logic" needs to lie in the database.

I don't typically like to see SQL in the client application at all, preferring to have it in a configuration file or in stored procedures. This keeps the interface between the application and the database well-defined.