We Are Communication Architects

Building brand awareness through content creation and community engagement.

August 16th, 2007

Data Access and Business Logic

Since I’ve started web development, I’ve come across many ways to handle data access and business logic. Some of these have been good, many were bad, and some had potential but not for that particular project. I’ve decided I would bring up some of the better designs I’ve come across and discuss the benefits and problems I’ve experienced with each, and hopefully come up with a decent idea of when each should be used.

Ad Hoc

The basic premise of this is simple; put data access code and business logic anywhere that needs it. If you have a web page that allows the user to add, edit, and delete users, then put the code to access and handle adding, editing, and deleting users directly in the [server side] code of that page. The biggest benefit of this setup is flexibility. Since the code of every page has its own set of data handling procedures, you don’t have to worry about how the changes to one part is going to affect the entire project. This also allows you to just get the data you need at the time, which can increase performance. However, with this, you’ll often end up with the same code scattered across most of your files. This makes even minor changes a nightmare.

Benefits flexibility, performance

Problems code bloat, increased maintenance time/costs

When to Use This should really only be used in a couple of situations, small read-only sites and working models. Small read-only sites are ones that don’t really have any business logic to them. They basically spit out the same data no matter who the user is and what they do. Working models are the projects where you need to get a solution out there, but you don’t yet know the problem set well enough to create a well designed system. So, to move forward, you create a working model of the final project until you have identified enough of the problem set to replace it with a viable solution. Choose wisely though, plans often change and you may end up having to maintain that working model longer than you planned.

Datasets And Business Objects

While these methods can become two very different things, I’ve grouped them together because they usually tend to end in the same result. The basic premise behind these is having one set of objects that handle accessing the data and another for managing that data after its been retrieved. This method can make front end coding very quick and easy as you can now call simple well defined functions like:

$customer = Customer::GetCustomer($id);
$orders = $customer->getOrders();

While this often works out very well for most of the front end development, making changes to handle any unforeseen business requirements (yes, these do change, often) can be like adding a bathroom to a finished house. Major changes like this will often need to start in the database and weave their way through the data and business layers. A lot of time for what the boss probably thinks is a minor change. I’ve also run into an issue with performance using this method. Unless I’ve spent way too much time breaking down the business classes into the smallest data chunks I could think of, the program is almost always having to retrieve way more data than it actually needs. For instance, if there is a page that only needs to print out all the customer names who purchased Uncle John’s Bathroom Reader, then either the “Person Name” would have to be handled as a separate business object from the rest of the customer class or the software is having to wastefully retrieve each customer’s address, phone number, etc.

Benefits easier front end development, lower coupling between database logic and application logic, easier minor business logic changes

Problems wasted data transfer, major changes can be expensive, performance

When to Use Most projects will work best with this design. Its compartmentalized logic means that most changes will be relatively simple. The compartmentalized logic also means the developers only need to know about one section of the project at a time, which works well for multi-developer projects where people may come and go. If performance is a major concern, consider spending a great deal of time in pre-development design.

Logic First

This is the latest one I’ve discovered, and so far, its gone well. The basic idea is to move all the business logic to the database itself. Not only will this lower the number of trips to the database to retrieve intermediate logic data, but it also makes sure that any other applications that use the same data have the same rules applied. Through the use of stored procedures, transactions, and views a single query can now verify the customer’s credentials, input the order, and output the receipt info, all while being able to undo the entire thing in case of an error. With the business logic now contained within the database, data access can be setup in data managing classes, ad hoc, or both, leaving a lot of flexibility. This method is still relatively new to me, so I haven’t found many problems yet other than the fact that from experience, most developers don’t know enough about T-SQL to write all the needed business logic.

Benefits flexible data transfer, easier front end development, localized business logic, performance

Problems readability by most developers, debugging sql is much more difficult than other code, simple logic is more complex

When to Use Since writing T-SQL can be much more difficult and wordy to write, I would leave this to medium to larger projects. With a lot web projects moving towards AJAX integration, I prefer this to the conventional dataset design, as this allows less calls back and forth between the code and database, giving a much more responsive user experience.

About the Author
Michael Pretty is an application developer for the Voce Connect Platforms team with a background in developing for PHP, mySQL, WordPress and a handful of other environments. Follow him on Twitter @prettyboymp

Filed in Programming

Add Your Comment4 Responses to “Data Access and Business Logic”

sean on August 16th, 2007 at 7:55 pm

Nice… Yeah that “Ad Hoc” one can be a real pain…

anonymous coward on August 21st, 2007 at 4:39 am

wish i could read your site, but the font is too pale

nick on August 21st, 2007 at 9:01 am

Hi Anonymous Coward,

I appreciate the feedback! This site is a labor of love for us and we want as many people as possible to enjoy it. To this point we haven’t had any other complaints of readability, but it’s something we’ll keep an eye on.

robert on August 21st, 2007 at 3:28 pm

yes, but … disadvantages: implementing procedural mechanics in an environment like T-SQL (debugger? code-folding editor? huh?). And you get the advantages of code independence with the 3-tier model, too – just make sure the top level only talks to the middleware, not directly to the data. Since you can build the middleware in a real language with real tools, where’s the downside?