From: Mario Splivalo <mario.splivalo@megafon.hr>
To: pgsql-sql@postgresql.org
Subject: ENUM vs DOMAIN vs FKyed loookup table
Date: Wed, 08 Apr 2009 19:08:55 +0200
Message-ID: <49DCDA27.4090901@megafon.hr> (raw)
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
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: mario.splivalo@megafon.hr
Subject: Re: ENUM vs DOMAIN vs FKyed loookup table
In-Reply-To: <49DCDA27.4090901@megafon.hr>
* 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