pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedENUM vs DOMAIN vs FKyed loookup table
6+ messages / 5 participants
[nested] [flat]
* ENUM vs DOMAIN vs FKyed loookup table
@ 2009-04-08 17:08 Mario Splivalo <mario.splivalo@megafon.hr>
2009-04-08 19:42 ` Re: ENUM vs DOMAIN vs FKyed loookup table Peter Eisentraut <peter_e@gmx.net>
2009-04-12 18:08 ` Re: ENUM vs DOMAIN vs FKyed loookup table Dirk Jagdmann <jagdmann@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Mario Splivalo @ 2009-04-08 17:08 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ENUM vs DOMAIN vs FKyed loookup table
2009-04-08 17:08 ENUM vs DOMAIN vs FKyed loookup table Mario Splivalo <mario.splivalo@megafon.hr>
@ 2009-04-08 19:42 ` Peter Eisentraut <peter_e@gmx.net>
1 sibling, 0 replies; 6+ messages in thread
From: Peter Eisentraut @ 2009-04-08 19:42 UTC (permalink / raw)
To: pgsql-sql; +Cc: Mario Splivalo <mario.splivalo@megafon.hr>
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ENUM vs DOMAIN vs FKyed loookup table
2009-04-08 17:08 ENUM vs DOMAIN vs FKyed loookup table Mario Splivalo <mario.splivalo@megafon.hr>
@ 2009-04-12 18:08 ` Dirk Jagdmann <jagdmann@gmail.com>
2009-04-14 13:43 ` Re: ENUM vs DOMAIN vs FKyed loookup table Mario Splivalo <mario.splivalo@megafon.hr>
2009-04-15 12:57 ` Re: ENUM vs DOMAIN vs FKyed loookup table Jasen Betts <jasen@xnet.co.nz>
1 sibling, 2 replies; 6+ messages in thread
From: Dirk Jagdmann @ 2009-04-12 18:08 UTC (permalink / raw)
To: Mario Splivalo <mario.splivalo@megafon.hr>; +Cc: pgsql-sql
> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ENUM vs DOMAIN vs FKyed loookup table
2009-04-08 17:08 ENUM vs DOMAIN vs FKyed loookup table Mario Splivalo <mario.splivalo@megafon.hr>
2009-04-12 18:08 ` Re: ENUM vs DOMAIN vs FKyed loookup table Dirk Jagdmann <jagdmann@gmail.com>
@ 2009-04-14 13:43 ` Mario Splivalo <mario.splivalo@megafon.hr>
1 sibling, 0 replies; 6+ messages in thread
From: Mario Splivalo @ 2009-04-14 13:43 UTC (permalink / raw)
To: Dirk Jagdmann <jagdmann@gmail.com>; +Cc: 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ENUM vs DOMAIN vs FKyed loookup table
2009-04-08 17:08 ENUM vs DOMAIN vs FKyed loookup table Mario Splivalo <mario.splivalo@megafon.hr>
2009-04-12 18:08 ` Re: ENUM vs DOMAIN vs FKyed loookup table Dirk Jagdmann <jagdmann@gmail.com>
@ 2009-04-15 12:57 ` Jasen Betts <jasen@xnet.co.nz>
2009-04-15 16:43 ` Re: ENUM vs DOMAIN vs FKyed loookup table Chris Browne <cbbrowne@acm.org>
1 sibling, 1 reply; 6+ messages in thread
From: Jasen Betts @ 2009-04-15 12:57 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ENUM vs DOMAIN vs FKyed loookup table
2009-04-08 17:08 ENUM vs DOMAIN vs FKyed loookup table Mario Splivalo <mario.splivalo@megafon.hr>
2009-04-12 18:08 ` Re: ENUM vs DOMAIN vs FKyed loookup table Dirk Jagdmann <jagdmann@gmail.com>
2009-04-15 12:57 ` Re: ENUM vs DOMAIN vs FKyed loookup table Jasen Betts <jasen@xnet.co.nz>
@ 2009-04-15 16:43 ` Chris Browne <cbbrowne@acm.org>
0 siblings, 0 replies; 6+ messages in thread
From: Chris Browne @ 2009-04-15 16:43 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2009-04-15 16:43 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-04-08 17:08 ENUM vs DOMAIN vs FKyed loookup table Mario Splivalo <mario.splivalo@megafon.hr>
2009-04-08 19:42 ` Peter Eisentraut <peter_e@gmx.net>
2009-04-12 18:08 ` Dirk Jagdmann <jagdmann@gmail.com>
2009-04-14 13:43 ` Mario Splivalo <mario.splivalo@megafon.hr>
2009-04-15 12:57 ` Jasen Betts <jasen@xnet.co.nz>
2009-04-15 16:43 ` Chris Browne <cbbrowne@acm.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox