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 values
RECURRINGCHAR2 (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