DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=Q3Q8zU+EWfaFVqO/2t6IHtRxWGM3k2vgWtNm353w1ycSqz1zDVLmIQFioXV4qgn1DE
	hAYe5xdRXccVIcu0/0rdbAWe0FyDiOP2+F4apmjW5KaMULcFSY5OV5iVdm31ndp+VZ3w
	Zvxaeiipjgm4Sdp/gMmac0L62RQ/yHrcj2en0=
MIME-Version: 1.0
In-Reply-To: <49DCDA27.4090901@megafon.hr>
References: <49DCDA27.4090901@megafon.hr>
Date: Sun, 12 Apr 2009 11:08:38 -0700
Message-ID: <5d0f60990904121108y7f5dbb7aydf2105b68bceab12@mail.gmail.com>
Subject: Re: ENUM vs DOMAIN vs FKyed loookup table
From: Dirk Jagdmann <jagdmann@gmail.com>
To: Mario Splivalo <mario.splivalo@megafon.hr>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

> 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