Dirk Jagdmann wrote:
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
);
I did a little bit of testing, I created two types, one domain and one enum:
CREATE DOMAIN domain_code_type
AS character varying
NOT NULL
CONSTRAINT domain_code_type_check CHECK (VALUE IN
('ApprovedByAdmin', 'Unapproved', 'ApprovedByEmail'));
CREATE TYPE enum_code_types AS ENUM
('Unapproved',
'ApprovedByEmail',
'ApprovedByAdmin');
And two tables:
CREATE TABLE codes__enum
(
code_id integer NOT NULL,
code_type enum_code_types NOT NULL,
CONSTRAINT codes__enum_pk PRIMARY KEY (code_id)
)
CREATE TABLE codes__domain
(
code_id integer NOT NULL,
code_type domain_code_type NOT NULL,
CONSTRAINT codes_domain_pk PRIMARY KEY (code_id)
)
I filled them with 12.000.000 rows of the very same data. Here are size
differences:
enum_test=# select * from pg_size_pretty(pg_relation_size('codes__enum'));
pg_size_pretty
----------------
415 MB
(1 row)
enum_test=# select * from pg_size_pretty(pg_relation_size('codes__domain'));
pg_size_pretty
----------------
520 MB
(1 row)
Inserting data to _domain table is a bit slower than to _enum table.
I also created two tables 'linked' with primary keys, just one used
code_type::varchar as FK field, while other used code_type::integer as
FK field (but, in referenced table I need to have code_type_description
or whatever). I filled them with the same data.
Size of the table where FK is varchar is the same as in _domain table,
while table where FK is integer is same as in _enum table.
Enums and pain to administer - changing enum type definition for a table
where I have 12.000.000 rows takes almost forever! Domains are a bit
more flexibile, yes.
And, yes, 'person sex' is very good example on 'when to use enums'. My
example is poor for enums and domains. Separate table referenced with
foreign key seems like best solution because I have no idea if the
client might want to add 'ApprovedButThenRevoked' or
'ApprovedWhenNotDrunk' code types in the future.
Mike
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
