> 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. -- ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq