Wednesday, September 5, 2007

Business Logic: In the Database or in the Application

When I started in the Data Warehouse, I began subscribing to as many BI/Data Warehousing blogs that I could find (there don't seem to be lot of them). Intelligent Enterprise (Roger Kimball) and Bill Inmon seem to be the most popular or well known. I also read Mark Rittman (and Mr. Mead), David Aldridge, Lewis Cunningham and Dratz who all seem to have more of an Oracle lean.

Today I read a post from Dan Linstedt titled "How Data Models can Impact Business." I followed the rather lengthy, but descriptive post, until I got to this part:

"Ok, I kind-of buy it, but what about Referential Integrity, when should that be enforced?
In two places:
1) When the data is captured within the application capturing it - it would clean up a LOT of these source systems, and put much more rigorous business logic (and cleaner data) into the source systems to begin with.
2) When the data is "released" for users, to reports, to screens, to output. This is when reusable common services / routines for getting data out are helpful. They implement the referential integrity in the application layer."

Now, I haven't been in IT all that long (5+ years now), but I put as much business logic into the database as I possibly can. I use the front end (APEX, woohoo!), to handle row color or something along those lines.

If you start with a good, flexible model, have a strong database team (DBAs, Developers) and you build a good API to your physical model (no INSERT, UPDATE or DELETE to any users, the only entry point to your tables being your API), flexibility and maintainability should not be a problem. Making changes should not entail a monumental effort. I suppose if the application in Mr. Linstedt's article is the only point of entry into the tables, I probably wouldn't really disagree (he's just moved it from my database API to his application), but then you have a giant bucket. Why not just use what you have paid for and build it in the database? That way, more than one application can use the same API over and over.

3 comments:

Paweł Barut said...

I totally agree with. Good integrity constraints, and good API is a key factor to have data in good quality.

Anonymous said...

Hi,

I read through your post, and I'd like to state that my comments in my original blog entry are directed at EDW, specifically very large EDW sets. In particular, enterprise data warehouses containing 50+ TB of raw data.

I'd also like to point out that specifically to data warehousing, often times people confuse the EDW with the Operational System, as a way to "be" an enforcer of the data at the database level.

This is the job of the operational system (source / system of record), not the job of the EDW... My appologies if I wasn't clear in my original post.

Thank-you kindly,
Daniel Linstedt

oraclenerd said...

I figured that was what you were saying (I do read your blog regularly), but it just went against everything I have learned. Of course I have not been in the data warehousing environment for very long (less than a year) and I will promise to keep an open mind going forward.