Re: New SQL Datatype RECURRINGCHAR

This is not a good idea. You are probably coming from mysql background (no
offense :).

See comments inline.

On Fri, 29 Jun 2001 dbennett@jade.bensoft.com wrote:

> Idea for a new SQL Data Type:
> 
> 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.
You should instead have another table with two columns, order_status_id
and order_status_desc, and join with it to get your data.
> 
> Advantages:
> 
>  - Storage space is optimized.
> 
>  - a query like:
> 
>     select distinct {RECURRINGCHAR} from {table} 
> 
>    can be radically optimized
select distinct order_status_desc from order_status_lookup

>  - Eliminates use of joins and extended knowledge of data relationships
>           for adhoc users.
For adhoc users, you can create a view so they won't be aware of joins. 

> 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.
No, that is against good database design and any database normalization.

-alex