View Single Post
  #13  
Old March 15th, 2005, 07:35 PM
rpw
external usenet poster
 
Posts: n/a
Default

Thanks for the reply and explanation Tim. I guess I was thinking "short cut"
as saving time, but for my skill levels in Access, having the separate table
for colors works faster for me. Although I am trying to input Jamie's SQL
DDL into an Access query just so that I 'understand' how it works.

"Tim Ferguson" wrote:

"Jamie Collins" wrote in
oups.com:

Essentially yes, although a CHECK constraint is more powerful than a
Validation rule e.g. a CEHCK can reference other columns in the same
row and columns in other tables.


A validation rule _is_ a check constraint: the Access UI coverts a VR
straight into a db rule. The only difference is that the UI still
appears uses a primitive version of ADO/ DAO and using SQL- DDL directly
does provide more functionality, but it's only a UI limitation.

Why is this a 'short cut'?


To me, usng a database schema to hardwire contraints that are purely
abitrary is bad practice, and is a short cut round creating a proper
table and suitable FK rules.

At one end of the extreme would be a validation rule (qua check
constraint like, "IN ('Red', 'Blue', 'White')", which would clearly be a
disaster when the first yellow car came along. Close down the database,
call in all the replications, run the DDL commands, recompile and
reindex the database, reconnect front ends, hope the outside code still
works, etc etc (okay, I am exaggerating, but you get the picture).
Compare that with just adding one record to a table.

At the other end of the extreme is an attribute like sex. There really
is little chance of "IN ('M', 'F')" ever having to be changed in our
lifetimes; but then again medical and surgical databases have to be able
to handle Unknown, Both, UsedToBeOneAndNowIsTheOther and so on.

In the end, db design is a skill as well as a technique -- the best
designs are the ones that make the best compromises^W trade- offs
between judicious short cuts and hopeless prolixity. But before doing
that, you do need to have explored both the long way round and the short
one in order to know what you lose or gain either way.

All the best



Tim F