Data Logic and Business Logic

The 3-tier or n-tier design has been widely adopted for very good reasons: separation of concerns; divide and conquer...

One doctrine of the 3-tier design is to treat the data tier as data storage and do not implement any business logic inside your database. However, this does not mean that database should not have any logic. On the contrary, any data specific logic should be implemented in the database, not in the business tier.

I recently read an interesting post on using Hibernate Interceptor classes to access the table in the desired schema. The end result of this implementation is to hide the fact that the application is dealing with tables in multiple database schemas so that SQL queries don't have to be littered with 'schemaName.tableName' everywhere. Of course, a simpler solution to this problem is to create a public Synonym in the database.

In my opinion, this 'table routing' logic should be implemented in the database server as it is database design/deployment specific. On the other hand, Hibernate is an ORM framework and logically belongs to the data tier in the 3-tier architecture, so it is not wrong to do it in there, especially if the database server does not support synonyms.

Another real-life example of data logic is in high volume tables - e.g. in Telco billing applications, there are millions of call event records a day; so it is common practice to partition that call event table. In the earlier days, when database servers did not support table partitioning (e.g. SQL Server 2000), DBAs have been manually segmenting tables (e.g. by creating multiple tables: CallEvent_20080201, and CallEvent_20080202, etc.). Obviously the application should not implement the logic of which table to use when executing a query (e.g. get me all call records for today), but let the database handle it - a common practice is to create database view to consolidate/union the manually partitioned tables. An advantage of implementing it in the database is that when you upgrade or migrate to another database server which supports table partitioning (e.g. SQL Server 2005+, Oracle 8+) your code need not change (OK, if you are using ORM, the mapping file may need some modification).

To make a better design decision, the system designer/architect and the data designer/architect must jointly design the system in a collaborative manner.