On Wed, 25 May 2011 17:23:26 -0500, Peter Koczan <pjkoc...@gmail.com> wrote:
> On Tue, May 17, 2011 at 11:23 PM, Seb <splu...@gmail.com> wrote: >> Are there any guidelines for deciding whether to 1) create an enum >> data type or 2) create a table with the set of values and then have >> foreign keys referencing this table? Some fields in a database take >> a small number of values, and I'm not sure which of these routes to >> take. The enum data type seems like a clean way to handle this >> without creating a constellation of tables for all these values, but >> if one wants to add a new label to the enum or make changes to it at >> some point, then the tables using it have to be recreated, so it's >> quite rigid. Have I got this right? Thanks. > I think your choice depends on a few things: > 1 - How do you want to interact with the tables? What I mean is, are > you planning on querying, inserting, or updating data to those tables > via text or will you need to join to your reference table? If you > don't want to join, you'll either need to use enum types, use views > (which can be a pain if you want to update a view), or > duplicate/reference the text directly (which is slow and a bad idea > for several reasons). > 2 - How much can you tolerate downtime or a busy database? Changing > types is a single transaction and requires an exclusive lock. On small > tables this is negligible, but on big tables it can require downtime. > 3 - How often do you really expect changes to the enum type? If adding > a new value to an enum type is truly a rare event, it's . If it's > frequent or regular, you should probably have a table. > I've used both of these approaches and I've found enum types to be > well worth any trouble to drop/recreate types. The changes I've made > have been rare, and I've been able to schedule downtime pretty easily, > so it made the most sense for me. > Also, Postgres 9.1 allows adding values to enum types, so you could > always use that when it is finally released. These are great guidelines, thanks. -- Seb -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
