Re: How to modify ENUM datatypes?

On Sat, 2008-04-26 at 20:33 -0400, Robert Treat wrote:
> I think one of the best examples of this is the movie rating system (which I
> blogged about at
> http://people.planetpostgresql.org/xzilla/index.php?/archives/320-PostgreSQL-8.3-Features-Enum-Datatype.html
> )
>
> It's a good example of setting pre-defined values that really can leverage the
> enum types custom ordering. It also showcases the idea of data definitions
> that "should never change", but that do changes every half dozen years or so.
> Now you can argue that since it is expected that the ratings might change in
> some way every few years that an enum type is not a good choice for this, but
> I feel like some type of counter-argument is that this is probably longer
> than one would expect thier database software to last. :-)
>

Let's say you have ratings A, B, and D for 5 years, and then you add
rating C between B and D.

If you have a constant stream of movies that must be reviewed, then the
addition of a new rating will necessarily take some fraction of the
movies away from at least one of the old ratings. In that case, is an
old B really equal to a new B?

Similar concerns apply to other changes in ENUMs, and for that matter,
they apply to the FK design, as well.

I would say the *actual* rating is the combination of the rating name,
and the version of the standards under which it was rated.

Regards,
Jeff Davis