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