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.
>
>
To be honest, I forget why. Possible because we also needed to be able
to get a list of allowed values, although I don't know how one does that
in mysql. Maybe because it just seemed like a good idea at the time and
nobody spoke up against it.
>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.
>
>
>
That won't make it easier to change the ordering or the value set, which
some people seem concerned about.
But it too might be a nice feature. I suspect it would be a lot more
work than simple enums, for which there is significant demand.
cheers
andrew