Re: ENUM vs DOMAIN vs FKyed loookup table

> 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