On Thu, Oct 27, 2005 at 11:07:19PM -0400, Rod Taylor wrote: > > The other issue is ease of use. > > > > We used lookup tables in bugzilla when it was converted to work with > > Postgres. But many users will find having to do that annoying, to say > > the least. I think there's a very good case for providing true enums. > > Then why did you use lookup tables instead of a varchar and a > constraint? Probably performance. > > A much more general purpose but just as good solution would be the > ability to create a hidden surrogate key for a structure. > > CREATE TABLE status (code varchar(20) PRIMARY KEY) WITH SURROGATE; > CREATE TABLE account (name varchar(60), status varchar(20) references > status); > > Behind the scenes (transparent to the user) this gets converted to: > > CREATE TABLE status (id SERIAL UNIQUE, code varchar(20) PRIMARY KEY) > WITH SURROGATE; > CREATE TABLE account (name varchar(60), status integer references > status(id)); > > > SELECT * FROM account; would be rewritten as > SELECT * FROM (SELECT name, code FROM account JOIN status USING (id)) AS > account; > > Enum might be good for a short list of items but something like the > above should be good for any common value that we manually create > surrogate keys for today but without the clutter or the application > needing to know. > > If PostgreSQL had an updatable view implementation it would be pretty > simple to implement.
I'm not quiet following the WITH SURROGATE bit, but what you've described certainly looks valuable. Note that I would still want to be able to get at the raw numeric values in some fasion. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
