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

On Wed, 24 May 2006 22:27:15 +0100, Paul <paul_at_see.my.sig.com> wrote:

>
>
>
>This is a question about database design philosophy and opinion more
>than anything else.
>
>
>Imagine we have a person table
>
>ID (int), Title(VARCHAR(22)), Gender(Char(1)), State(Char(2)),
>First_Name..........
>
>
>Something along those lines.
>
>Now, we all know that it makes no sense to have a gender table - a
>simple check constraint = 'M' or 'F' is all that's required.
>
>For Title, the situation is slightly different. You could have Ms.,
>Mr., Mrs. - but what happens if you want to add 'Fr.', 'Col.',
>'Excellency', 'Her Imperial Highness', 'His Holiness'? All, of course
>in the knowledge that you would never have more than 20-25 entries in
>the Title field.
>
>What's better in this case?
>
>To create a table called Person_Title and develop a user screen to
>maintain it (adding one record every year or so, and then very few for
>years), or to permit in these circumstances the maintainer of the
>tables to issue DDL against the Person table and add to the
>check_constraint clause (said maintainance being done at night or
>something like that). What about indexes on this table - IOT?
>
>Bitmap index on the Person_Table on the Gender field?
>
>
>Assuming the State field has the American States - say 50 - 55 entries
>which are unlikely to change - which is better - a long check
>constraint or a referencing table?
>
>
>Where does one start drawing the line between check constraints and
>tables - i.e. the maintainance and verification of Foreign Keys?
>
>
>Any ideas, discussions, URL's, references or other stuff welcome.
>
>
>Paul...

Hardcoded check constraints should be limited to two or three values. The basic question is:
where do I put it? Do I put in a function, or do I put it in a table?

If you think the # of values is going to change, or the values are going to alter, and you don't want to have to change the app or the check constraints: put them in a table.
If the number of values is more than 3: always put them in a table. You might forget a state or miscount them.

--
Sybrand Bakker, Senior Oracle DBA