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

"Paul" <paul_at_see.my.sig.com> wrote in message news:v5j97250dsgqs8hpgskeh791mu898ji3m1_at_4ax.com...
>
>
>
> 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...
>
>
> --
>
> plinehan __at__ yahoo __dot__ __com__
>
> XP Pro, SP 2,
>
> Oracle, 9.2.0.1.0 (Enterprise Ed.)
> Interbase 6.0.1.0;
>
> When asking database related questions, please give other posters
> some clues, like operating system, version of db being used and DDL.
> The exact text and/or number of error messages is useful (!= "it didn't
> work!").
> Thanks.
>
> Furthermore, as a courtesy to those who spend
> time analysing and attempting to help, please
> do not top post.

That's an interesting question.

It reflects the fact that Oracle does not allow you to define Domains - so tables are the next best thing (although you might be able to do something with user-defined types).

However, if you guarantee that a certain list of values is the domain for just one column in just one table then a constraint makes sense.

If you want to use the same domain for multiple columns, then the only safe option is to make it a table so that you can avoid the risk errors in maintaining multiple copies of the same list of values in different constraints.

As a table, the domain probably ought to be a single column IOT. Of course, if you have a 'translation' or 'description' for each domain value, then it isn't really a domain, it is an entity and ought to be represented by a table anyway.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html