Home » Programming » Object Oriented Design Pattern for Database Driven Applications

Object Oriented Design Pattern for Database Driven Applications

I’ve been working with database driven applications and object oriented programming for a while now and every time I try to marry up the two patterns I cringe.  Many people seem to love ActiveRecord pattern but it has many shortcomings in that it can only deal with very simple applications and everything is in one place.  Likewise DataMapper at the other extreme is quite complex and you end up doing a lot of shuffling of data in the application.  However what’s even worse is that in complex database driven applications you often want to do things like retrieve results which can span multiple tables and you only need to take a little bit of data from each table.  A good example of this is an Order that was placed by a Customer who belongs to an Account.  With a full object oriented domain model you’d have the following options none of which are great…

  • Fully load all objects – You’d load Order, Customer and Account objects, perhaps as a single query, but still you end up retrieving way more data than you actually need in this case.
  • Partially load objects – You’d need to load Order, Customer and Account objects only with data that you need, but then those objects aren’t really fully functional objects anymore and that’s a bit ugly.
  • Add fields into main object – You could add required Customer and Account fields into the order object, but that becomes a bit ugly as for example you might need Customer Full Name and you sure don’t want to duplicate the logic and where do you stop.  You also don’t necessarily need all the Order fields.
  • Create custom object – You could create a custom object with just the fields you need, but once again if you need Customer Full Name you’re going to be duplicating logic from the Customer object or will need to find some other way around it, which once again isn’t all that nice.  What happens then if you have lots of these objects… Duplication is going to become unmanagable very very quickly.
    Besides these options there really aren’t any other options that I could think of.

The amount of data you pull from the database and write back into the database is very important from a performance consideration as is code re-use from a code maintanance consideration.  So I think a far better solution is actually to use the Table Module pattern with Table Data Gateway pattern as required.  This is actually pretty consistent with what Microsoft advocates in their Patterns and Practices books and can work very well for pretty much any language and ESPECIALLY for languages such as PHP where arrays or objects with dynamically added attributes can be used as Data Transfer Objects.

Now, to define how this should work in a bit more detail.

The first step is we effectively need to separate the logic and the data, then we also want to isolate the data operations from the actual business logic, especially if we’re going to use a driver/provider pattern to support multiple databases.  So firstly we’ll have an object oriented database access classes, which pure handle dealing with the database.  In .NET these already exist in the System.Data namespace and in PHP there is a need to write a wrapper for what’s there just to make database interation simpler.  For .NET there is also a need to write some helpers/wrapper if working directly with DataReader to help managing DBNulls.  If using Strongly Typed Data Sets or LinqToSql or LinqToEntities there is generally no need as these already handle this for you.  For PHP if working with multiple database there’s also a need to write an abstraction layer.

The second step is to created Data Transfer Objects / Entities for actually holding the data that you need.  If using .NET Typed Data Sets already do this as do LinqToSql and LinqToEntities (As these generate both the data and the ORM to map the data between Entity objects and SQL and move the data between objects and the database).  If using PHP there isn’t really a need to do this as it’s common to use arrays or objects with dynamic properties to do this.

The third step is to create a layer that is responsible for performing the data operations, a Data Access Layer.  Something like DataManager is a good name for this.  Basically this would incapsulate the logic to create the actual queries.  For example, OrderDataManager may have methods like GetAllOrders, GetRecentOrders, GetUnfulfilledOrders, SaveOrder, SaveOrders, etc… This layer is only responsible for dealing with data and contains no logic for how data interacts.  If using .NET this would most likely be just a simple wrapper for Typed Data Sets, LinqToSql or LinqToEntities or if creating SQL manually or calling stored procedures than calls to those.  In PHP this would be a set of classes that incapsulate all the SQL.

The forth step is to create the Business Logic Layer… This layer is responsible for all the business logic and doing any calculations, etc.  So for example the OrderManager may call the CustomerManager to do something like get the Customer’s Full Name based on the data that’s been retrieved and the Account Manager to do something too.  Any data access is performed by calling the Data Access Layer or other Business Logic Layer objects.  Other objects that have nothing to do with any data (e.g. Payment Gateways) will also happily reside in this layer.  If the application is relatively small and only uses SQL that will work on all the databases it’s beend designed to work with, then it may also be possible and make sense to combine the Business Logic Layer with the Data Access Layer in the one object.  If working with MVC this would be the Model.  Either way, everything so far would actually fall withing the Model part of MVC.  That is, multiple layers would make up the Model, to achieve better separation of concerns and scalability.

A good example of how this pattern is implemented can be observed in the NopCommerce solution for anyone writing in .NET, except it would be better to implement the managers as normal classes rather than static classes, as that would allow for use of inheritance and other OO features.  OpenCart is a pretty good example of how to do it in PHP.

Basically the main idea is to separate the data from the logic and the behaviour where the logic to actually manage the data is implemented in an object oriented way.  By doing it this way you can still manage pretty complex logic in an object oriented way, but not have the issues with the mismatch between relational and object oriented patterns.

Categories: Programming Tags:
  1. January 29th, 2011 at 03:23 | #1

    Thank you for your article and for the sensible view on these MVC/ORM/DAO things. Stacked data access layers can actually simplify the development of large scale projects but choosing wrong tools and overuse of them can make performance degradation a real headache. And getting to lower levels comes to town, like we did rewriting critical pieces of C code in pure assembler before.

  1. No trackbacks yet.

4 − two =