Re: CHECK versus a Table for an enumeration

On Mon, 22 Dec 2003, Melanie Bergeron wrote:

> Hi all!
>
> I want to know what's better between these 2 solutions :
>
> CREATE TABLE user (
> ...
> user_type   text     CHECK(user_type='Root' OR user_type = 'Admin' OR
> user_type = 'Standard'));

Will you ever in your wildest dreams need more or different values in
user_type?  If not them go here.  I user this for well known, limited
sets - Male/Female.

> or the following :
>
> CREATE TABLE user_type(
> user_type_id     integer   PRIMARY KEY,
> user_type_desc text);
>
> CREATE TABLE user (
> ...
> user_type_id   integer,
> CONSTRAINT user_type_exists FOREIGN KEY (user_type_id) REFERENCES
> user(user_type_id));
>
> I am really confused so I'll wait for your advices.

This when there is a chance, any chance, you'll need to add to the list or
make changes to user_type_desc.


Rod
--
    "Open Source Software - You usually get more than you pay for..."
     "Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL"