Thread: enum data type vs table
Hi, 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. -- Seb
On Tue, May 17, 2011 at 11:23 PM, Seb <spluque@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. Hope this helps, Cheers, Peter
On Wed, 25 May 2011 17:23:26 -0500, Peter Koczan <pjkoczan@gmail.com> wrote: > On Tue, May 17, 2011 at 11:23 PM, Seb <spluque@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