Re: enums - Mailing list pgsql-hackers

On Thursday 2005-10-27 17:39, Michael Glaesemann wrote:
> On Oct 28, 2005, at 9:23 , Trent Shipley wrote:
> > On Thursday 2005-10-27 16:22, Andrew Dunstan wrote:
> >> Jim C. Nasby wrote:
<snip/>
> Relational databases already have a type for unordered sets: tables.
> IMO, if there's going to be a separate enumerated type, it should be
> more than just an alternative way of defining a set of key-value pairs.
>
> Michael Glaesemann
> grzm myrealbox com

Of course, what is an enumeration except an *ordered* list of key-value pairs; 
that is, a set with a built-in collation.

Glaesemann is right.  To me that implies that no database NEEDS an enumeration 
type.

We just started discussing it because it would greatly enhance MySQL 
migration.  Even more important, enumerations would be tremendously 
convenient (even if they are not strictly necessary).

Enumerations would be good marketing and good engineering.

The debate is about implementation:

1) Pure list (seems to be why MySQL does).  Each enumeration is a list.  If 
you want to create a variant collation for the list, create a new, parallel 
list. 

This is very straightforward but violates the "store once, read many times" 
principle of database design. 


2) Hybrid list + secondary collations.  You create and store an enumeration 
(call it enum_a).  If you want to reorder the underlying set, just declare a 
new collation for the enumeration (call the result enum_b).  enum_b is 
effectively a virtual enumeration.  The relationship of enum_b to enum_a is 
like that between a view and its table.

On the downside, this approach is a theoretical stew.  It should be relatively 
easy to implement.



3) Set + collation functions.  You define a set.  You define a collation for 
the set.  Having declared set_a and a collation_a you can then declare 
enumeration_a.  (Note that the result defined by the developer's collation 
function may not necessarily result in a _per se_ enumeration.)

This has the appeal of separating the symbol declaration from its ordering.  
Furthermore, of all the options it is the most powerful.  Unfortunately, it 
may be verbose, unintuitive, and the most difficult to implement.

There is probably no reason approach #1 or #2 could not be implemented using 
the machinery for approach #3 under the bonnet.  That way we could have 
something like:

CREATE SYMBOL SET  {possibly a disguised create table, but probably not for performance 
reasons}
CREATE COLLATION USING function_name
CREATE ENUMERATION
and
CREATE MYSQL_ENUMERATION. {probably just overload CREATE ENUMERATION}

===============================

http://dev.mysql.com/doc/refman/5.1/en/string-type-overview.html
http://dev.mysql.com/doc/refman/5.1/en/enum.html
(Note that unlike C enumerations MySql enumerations are two way and do some 
context dependent magic.)