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
Re: ENUM vs DOMAIN vs FKyed loookup table |
Список | 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 по дате отправления: