Thread: 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? Only recently I started using domains, but that seems to be painful, as Joshua Drake explains here: http://www.commandprompt.com/blogs/joshua_drake/2009/01/fk_check_enum_or_domain_that_is_the_question/ For instance, I have an application that tracks codes. I have table that looks like this: CREATE TABLE codes (code_id integer,code_value integer,code_type enum_code_type ) CREATE TYPE enum_code_type AS ENUM ('Unapproved', 'ApprovedByEmail', 'ApprovedByAdmin'); Now, let's say I have around 5.000.000 codes in my table. If I want to add new code type, i'm in deep pain. I can't do ALTER TYPE to change enum definition, I need to do hocus-pocus to achieve what I want. If I used domain, I can do ALTER DOMAIN DROP CONSTRAINT, ALTER DOMAIN ADD... Takes a bit of time, but it is more flexible than ENUMs. Joshua made some benchmarking of the enum/domain types, and it turns out that domain type is slowest one. I'll be posting some of my benchmarks soon. I abandoned the lookup tables because of the JOINining. What are your practices, when do you use ENUMs and when Domains? Mike
On Wednesday 08 April 2009 20:08:55 Mario Splivalo wrote: > What are your practices, when do you use ENUMs and when Domains? When given the choice, pretty much ENUMs. Domains weren't really conceived for this sort of thing in the first place, so it's good to move away from them.
> 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
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
On 2009-04-12, Dirk Jagdmann <jagdmann@gmail.com> 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). "lambda moo" has even more genders than that.
jasen@xnet.co.nz (Jasen Betts) writes: > On 2009-04-12, Dirk Jagdmann <jagdmann@gmail.com> 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). > > "lambda moo" has even more genders than that. I'm not so sure about that... I suspect what you're thinking of are "Spivak pronouns," which aren't indication of gender... Instead, they represent a way of declining to indicate gender. It appears that what LambdaMOO has is not a larger number of genders, but rather a large number of ways to *indicate* gender, some of which (e.g. - Spivak pronouns) actually decline to do so. This is more or less equivalent to setting TZ/PGTZ or LC_LANGUAGE to get the system to output values in some format that you prefer. It may be that LambdaMOO has some ways to indicate sexual preferences, but sexual preferences are not the same thing as gender. -- let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;; http://linuxdatabases.info/info/linuxxian.html "The only thing better than TV with the sound off is Radio with the sound off." -- Dave Moon