Re: extensible enum types

>> 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.

That's probably true, but if there was any question of needing to
associate such values with US states, it ought to be perfectly obvious
to everyone that enums are totally inappropriate. If that wasn't the
case, then their use is only highly questionable, at least IMHO. What
you're describing isn't really a lookup table as I understand the
term. It's just a table. Lookup tables typically have things in them
like the various possible states of another table's tuples. In my
experience, lookup tables generally have two columns, an integer PK
and a description/state.

>> 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.

The "not having to remember lookup value PK" point I made was very
much ancillary to my main point. Ideally, if you restore a schema-only
dump of your database, you shouldn't be missing anything that is
schema. Things like the possible states of a table's tuples are often
schema, not data, and should be treated as such.

--
Regards,
Peter Geoghegan