From: | Dirk Jagdmann <jagdmann(at)gmail(dot)com> |
---|---|
To: | Mario Splivalo <mario(dot)splivalo(at)megafon(dot)hr> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: ENUM vs DOMAIN vs FKyed loookup table |
Date: | 2009-04-12 18:08:38 |
Message-ID: | 5d0f60990904121108y7f5dbb7aydf2105b68bceab12@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
> When you need to choose between enum types, domain types or lookup tables
> with foreign keys, what do you usualy choose?
When I have a column with valid values that I know when writing my
tables and that will *never* change I use an enum. For example a human
gender type (and remember that there are 4 values for human sex if you
want to model it completely).
Otherwise a simple table with a primary key of type 'text' that is
used as a foreign key in the other table, so I can change/alter the
valid values later. No join needed! Remember that PK/FK do not always
have to be of type 'serial'.
The reason is, that for a user of the SQL language there is hardly any
difference in using an ENUM or a text type, since they are both
strings which must be enclosed in single quotes. Of course under the
hood for the PostreSQL languange parser and interpreter there is a
difference, but we can ignore that.
To revisit your example I would do it this way:
CREATE TABLE code_type (
t text not null primary key
);
insert into code_type values ('Unapproved'), ('ApprovedByEmail'),
('ApprovedByAdmin');
CREATE TABLE codes (
code_id integer,
code_value integer,
code_type text not null references code_type
);
--
---> Dirk Jagdmann
----> http://cubic.org/~doj
-----> http://llg.cubic.org
From | Date | Subject | |
---|---|---|---|
Next Message | Stuart McGraw | 2009-04-13 16:53:47 | Re: changing multiple pk's in one update |
Previous Message | Tom Lane | 2009-04-12 15:16:49 | Re: Fw: ERROR: operator does not exist: date ~~ unknown |