> 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql