Re: ENUM vs DOMAIN vs FKyed loookup table - Mailing list pgsql-sql

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