Should I generate a complex object in the database or data access layer?

Question

I'm working on an application for one of our departments that contains medical data. It interfaces with a third party system that we have here.

The object itself (a claim) isn't terribly complex, but due to the nature of the data and the organization of the database, retrieving the claim data is very complex. I cannot simply join all the tables together and get the data. I need to do a "base" query to get the basics of the claim, and then piece together supplemental data about the claim based on various issues.

Would it be better to when working with this data:

1. Generate the object in a stored procedure, where all of the relevant data is readily available, and iterate through a table variable (using SQL Server 2005) to piece together all the supplemental information.

2. Generate the object in the data access layer, where I have a little more powerful data manipulation at my disposal, and make a bunch of quick and simple calls to retrieve the lookup data.

3. Use an OR/M tool and map out all the complex situations to generate the object.

4. Something else.

EDIT: Just to clarify some of the issues listed below. The complexity really isn't a business issue. If a claim as a type code of "UB", then I have to pull some of the supplemental data from Table X. If the claim has a type code of "HCFA", then I have to pull some of the data from Table Y. It is those types of things. I hope this helps.

Answer 1

I'm not a fan of pushing business logic down to the persistence layer, so I wouldn't recommend option 1. The approach I'd take involves having a well-defined program object that models the underlying database entity, so ORM oriented, but your option 3 sounds like you're thinking of it as an onerous mapping task, which I really don't. I'd just have the logic necessary for loading up whatever you're concerned about with this object set up in methods on the program object modeling it.

Answer 2

As a general rule, I use a data access layer just to retrieve data (possibly from different sources) and return it in a meaningful manner.

Anything that requires business rules or logic (decisions) goes in my business layer.

I do not deviate from that choice lightly*.

It sounds like the claim you are generating is really a view of data stored in various places, without any decisions or business logic. If that's the case, I would tend to manage access to that data in the data layer.

*I'll never forget one huge system I worked on that got very over-complicated because the only person available to work on a central piece was an expert at stored procedures... so lots of the business logic ended up there.

Answer 3

I would use a stored procedure for security reasons. You don't have to give SELECT privileges to the claims tables that you are using, which sound somewhat important. You only have to give the user access to that stored procedure. If the database user already has SELECT privileges on the tables, I don't see anything wrong with generating the object in the data access layer either. Just be consistent with whatever option you choose. If you are using stored procedures elsewhere, continue to use them here. The same applies to generating the objects in the data access layer.

Answer 4

Think of the different ways you're planning to consume the data. The whole purpose of an application layer is to make your life easier. If it doesn't, I agree with @hoffmandirt that it's safer in the database.

Answer 5

Push decisions/business logic as high up in your applications code hierarchy as possible. ORMs/stored procedures are fine but cannot be as efficient as hand written queries. The higher up in your code you go the more you know what the data will be used for and have the information to intelligently get it.

Answer 6

One more vote for stored procedures in this case.

What you are trying to model is a very specific piece of business logic ("what is a claim") that needs to be consistent across any application that deals with the concept of a claim.

If you only ever have one application, or multiple applications using the same middleware, you can put that in the client code; however, practice shows that databases tend to outlive software that accesses them.

You do not want to wind up in a situation where subtle bugs and corner cases in redundant implementations make different applications see the data in slightly different ways. DRY, and all that.

Answer 7

Stored procedures are bad, m'kay?

It sounds like views would be better than stored procedures in this case.

If you are using .NET, I would highly recommend going with an ORM to get support for Linq.

In general, spreading business logic between the database and application code is not a good idea.

In the end, any solution will likely work. You aren't facing a make or break type decision. Just get moving, don't get hung up on this kind of issue.