ENUM vs DOMAIN vs FKyed loookup table

Поиск
Список
Период
Сортировка
От Mario Splivalo
Тема ENUM vs DOMAIN vs FKyed loookup table
Дата
Msg-id 49DCDA27.4090901@megafon.hr
обсуждение исходный текст
Ответы Re: ENUM vs DOMAIN vs FKyed loookup table  (Peter Eisentraut <peter_e@gmx.net>)
Re: ENUM vs DOMAIN vs FKyed loookup table  (Dirk Jagdmann <jagdmann@gmail.com>)
Список 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



В списке pgsql-sql по дате отправления:

Предыдущее
От: Stuart McGraw
Дата:
Сообщение: changing multiple pk's in one update
Следующее
От: Peter Eisentraut
Дата:
Сообщение: Re: ENUM vs DOMAIN vs FKyed loookup table