On Thu, Oct 15, 2009 at 2:52 PM, William Temperley > I recently
stopped using enums after reading this:
> http://www.commandprompt.com/blogs/joshua_drake/2009/01/fk_check_enum_or_domain_that_is_the_question/
> Using a foreign key to a single column table is pretty much as fast as
> an enum, is supported by most (all?) third party libraries, and avoids
> all the problems associated with enums.
> I guess the downside is the foreign key will take up more disk space,
> but that isn't an issue for me.
enums are a bit faster in the general case: you have a oid's worth of
storage. where enums have the chance to pay big dividends is indexes
_espeically_ if the enum is part of more complex ordering. This can
be worked around using the classic approach but the enum is simpler
and cleaner.
For example, suppose you have a requirement you have to pulling up
orders by account#/status
select * from order where .. order by account_id, status ;
if the status is an enum, you can take advantage of the enum's natural
ordering without the performance killing join for the natural ordering
or using function tricks in the create index statement to get good it
working properly.
This case comes often enough to justify enum's existence IMO.
merlin