extensible enum types
in [PgSql]
|
From: Andrew Dunstan on 19 Jun 2010 22:16 Simon Riggs wrote: > On Fri, 2010-06-18 at 11:50 -0400, Andrew Dunstan wrote: > > >> Thoughts? >> > > enum types exist as an optimisation-by-avoidance of referential > integrity. > > We're a relational database, so IMHO we should spend time performance > tuning RI. > > I don't accept your initial assertion at all. But in any case, these are not mutually exclusive. Your work tuning RI will not obstruct mine in making enums more useful, nor vice versa. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Peter Geoghegan on 19 Jun 2010 22:42 >> Thoughts? > > enum types exist as an optimisation-by-avoidance of referential > integrity. > > We're a relational database, so IMHO we should spend time performance > tuning RI. I take the view that they exist as a way of representing enumerations of application/domain values - if it's hard coded in the application, it's hard coded in the database by using an enum. This is why it isn't that big a problem that they cannot be amended - they ought to be very static, immutable values in the first place. I still think it would be handy to be able to append new values though, and not have to ALTER COLUMN USING to a new enum type. Besides, using enums in place of lookup tables doesn't really make much sense as an optimisation. It's very cool to be able to write queries like SELECT * FROM payments WHERE payment_type = 'cash', rather than having to recall time and again what the PK of cash is within your lookup table. -- Regards, Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: "Joshua D. Drake" on 19 Jun 2010 23:50 On Sun, 2010-06-20 at 03:42 +0100, Peter Geoghegan wrote: > >> Thoughts? > It's very cool to be able to write queries like SELECT * FROM payments > WHERE payment_type = 'cash', rather than having to recall time and > again what the PK of cash is within your lookup table. Ahem. That is what a natural key is for :) Joshua D. Drake > > -- > Regards, > Peter Geoghegan > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Peter Geoghegan on 20 Jun 2010 11:02 > Ahem. That is what a natural key is for :) Well, they have their own drawbacks that don't make them particularly appealing to use with lookup tables to ape enums. How many lookup tables have you seen in the wild with a natural key? People sometimes represent things like US states as enums. This is probably a mistake, because you cannot control or predict if there'll be a new US state, unlikely though that me be. You *can* control, for example, what types of payment your application can deal with, and you'll probably have to hardcode differences in dealing with each inside your application, which makes enums a good choice. In my earlier example, in addition to 'cash', there is a value for payment_type of 'credit_card' . There is a separate column in the payments table that references a credit_cards table, because credit cards are considered transitory. A check constraint enforces that credit_cards_id is null or not null as appropriate. I don't like the idea of having values in a table that aren't so much data as an integral part of your application/database. I think it's wrong-headed. That's why I am not in favour of your enums as a lookup table wrapper suggestion. -- Regards, Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: "Kevin Grittner" on 20 Jun 2010 11:24
Peter Geoghegan wrote: > How many lookup tables have you seen in the wild with a natural > key? Me? Personally? A few hundred. > People sometimes represent things like US states as enums. This is > probably a mistake, because you cannot control or predict if > there'll be a new US state, unlikely though that me be. More importantly, you're likely to need to associate properties with the state. Sales tax info, maybe a sales manager, etc. A state table can be a handy place to store things like that. > I don't like the idea of having values in a table that aren't so > much data as an integral part of your application/database. Yep, exactly why natural keys should be used when possible. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |