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(at)jade(dot)bensoft(dot)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