Skip site navigation (1) Skip section navigation (2)

Re: New SQL Datatype RECURRINGCHAR

From: Alex Pilosov <alex(at)pilosoft(dot)com>
To: dbennett(at)jade(dot)bensoft(dot)com
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: New SQL Datatype RECURRINGCHAR
Date: 2001-07-03 20:32:21
Message-ID: Pine.BSO.4.10.10107031627110.18587-100000@spider.pilosoft.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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


In response to

Responses

pgsql-hackers by date

Next:From: Jan WieckDate: 2001-07-03 21:03:57
Subject: Re: funny (cache (?)) bug in postgres (7.x tested)
Previous:From: Alex PilosovDate: 2001-07-03 20:26:38
Subject: Re: funny (cache (?)) bug in postgres (7.x tested)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group