This is rather like MySQL's enum. I still opt for the join, and if
you like make a view for those who don't want to know the data
structure.
--
Rod Taylor
Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. You cannot imagine why you ever felt
otherwise.
----- Original Message -----
From: <dbennett@jade.bensoft.com>
To: <pgsql-hackers@postgresql.org>
Sent: Friday, June 29, 2001 6:05 PM
Subject: [HACKERS] 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
>
>
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>