Re: Database Design Question - any suggestion?
Date: 9 Dec 2004 13:36:43 -0800
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. <<
- 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.
- 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:
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