Re: When to use check constraints and when to use small tables?

From: Brian Peasland <oracle_dba_at_nospam.peasland.net>
Date: Thu, 25 May 2006 14:55:49 GMT
Message-ID: <IzttHB.Lq4@igsrsparc2.er.usgs.gov>

>> If the number of values is more than 3: always put them in a table.
>> You might forget a state or miscount them.

>
> Ah, I see. This is a perspective I had forgotten about
> when framing my original post - I forgot to think about
> those application programmers for example who will
> have to write reports on the data in the app - it's true
> enough that anything beyond two or three is probably
> too much - I mean gender is OK but what about title?

Is the data a simple attribute or an entity? If the data is an entity, then put it in its own table. If the data is a simple attribute, then a check constraint is fine.

Take for example, a state. There are 50 of them in the US. To me, a state is an entity in its own right. The state has a name (attribute #1), and can have other attributes as well if you care to use them. The only attribute you care about may be just the state name. Or maybe you want the 2-letter abbreviation as well.

To me, GENDER is not an entity. It is an attribute of the person. Maybe that's just my opinion....but I'd use a check constraint on the GENDER attribute.

So entities deserve their own table. Attributes can be enforced with a simple CHECK constraint. But then there may be some things that you may have difficulty deciding if it is just an attribute or an entity on its own......

HTH,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Received on Thu May 25 2006 - 09:55:49 CDT