From: Dirk Jagdmann <jagdmann@gmail.com>
To: Mario Splivalo <mario.splivalo@megafon.hr>
Cc: pgsql-sql@postgresql.org
Subject: Re: ENUM vs DOMAIN vs FKyed loookup table
Date: Sun, 12 Apr 2009 11:08:38 -0700
Message-ID: <5d0f60990904121108y7f5dbb7aydf2105b68bceab12@mail.gmail.com> (raw)
In-Reply-To: <49DCDA27.4090901@megafon.hr>
References: <49DCDA27.4090901@megafon.hr>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jagdmann@gmail.com, mario.splivalo@megafon.hr
Subject: Re: ENUM vs DOMAIN vs FKyed loookup table
In-Reply-To: <5d0f60990904121108y7f5dbb7aydf2105b68bceab12@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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