New SQL Datatype RECURRINGCHAR

Idea for a new SQL Data Type:
RECURRINGCHAR

The idea with RECURRINGCHAR is treated exactly like a VARCHAR in it's
usage.  However, it's designed for table columns that store a small set of
repeated values (<=256 values). This allows for a great deal of savings in
the storage of the data.

Example:
 Query:   select count(*) from order Returns:   100,000
 Query:   select distinct status from order Returns:   OPEN   REWORK   PLANNED   RELEASED   FINISHED   SHIPPED

It's apparent that there is a lot of duplicate space used in the storage
of this information.  The idea is if order.status was stored as a
RECURRINGCHAR
then the only data stored for the row would be a reference to the value of
the column. The actual values would be stored in a separate lookup table.

Advantages:
- Storage space is optimized.
- a query like:
   select distinct {RECURRINGCHAR} from {table} 
  can be radically optimized
- Eliminates use of joins and extended knowledge of data relationships         for adhoc users.

This datatype could be extended to allow for larger sets of repeated
values:
RECURRINGCHAR1 (8-bit)   up to 256 unique column valuesRECURRINGCHAR2 (16-bit)  up to 65536 unique column values

Reasoning behind using 'long reference values':

It is often an advantage to actually store an entire word representing a
business meaning as the value of a column (as opposed to a reference
number or mnemonic abbreviation ).  This helps to make the system 
'self documenting' and adds value to users who are performing adhoc
queries on the database.

----
David Bennett
President - Bensoft
912 Baltimore, Suite 200
Kansas City, MO  64105