Re: Database Design Question - any suggestion?

>> This would force a rebuild of the constraint for every new product
type.<<

Why would I not wish to have new constraints on the new product types? What kind of business rule is "The new stuff can wrong, but the old stuff has to be right!" ?

>> I come from the type that leaves all checking to the application if
possible. Depending on your use of this database, I think response time is a very important factor for most and check constraints can slow things down in an OLTP environment. <<

1. If the data does not have to be right, then I can be VERY fast :) The answer is 42 and I don't care what the question was.
2. CHECK()constraints are usually not that expensive to execute. 80-90% of them are simple range checks and 10-15% are string predicates that can be done with the local version of SIMILAR TO (the Standard SQL version of grep()).

Since the backend is usually bigger and faster than the front ends, the constraints should take less clock time.

The remaining 5% or so of constraints that cost time involve other tables -- say, running a current total to prevent an overdrawn account. Unfortunately, even today, a lot of SQL engines have to do this with triggers and procedural code instead of the SQL-99 CHECK() constraints that allow multi-table predicates.

3) The problem with trusting just the front end to do the checking is that you have made a leap of faith that:

1. All current programmers know **and agree upon** ALL the business rules. That is, Peter reads "employees must be 18 years of age or older" as (age > 18) and Paul reads it as (age >= 18) and you're in trouble.
2. All future programmers know **and agree upon** ALL the business rules. Exactly how you arrange to hire these perfect programmers, I do not know.
3. When there is a change ("The legal age for alcohol is now 21, so change the hiring rules') , you have a way to propagate it thru ALL the application programs. And can produce an audit to prove that you changed those front ends, even the ones that were written by another shop.

I have a three-part series at www.dbazine.com on this topic. My arguement is for doing it both places. Simple errors trapping in the front end can get a lot of the typos before they get to the backend. Check digits and other things are easy to verify in applications code and get an immediate correction. The human being is still the slowest thing in the process unless you can type one word per nanosecond :) Received on Thu Dec 09 2004 - 22:36:43 CET