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

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.