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

RE: New SQL Datatype RECURRINGCHAR

From: "David Bennett" <dbennett(at)bensoft(dot)com>
To: "Alex Pilosov" <alex(at)pilosoft(dot)com>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Subject: RE: New SQL Datatype RECURRINGCHAR
Date: 2001-07-03 21:23:40
Message-ID: NEBBIHDFALGPGDHNLKBBAEHFCJAA.dbennett@bensoft.com (view raw or flat)
Thread:
Lists: pgsql-hackers
>> 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.

The idea is to simplify the process of storing and accessing the data.
Joins required
a deeper knowledge of the relational structure.  This also complicates
application
programming,  two tables must be maintained instead of just one.

>>     select distinct {RECURRINGCHAR} from {table}
>>
>>    can be radically optimized

> select distinct order_status_desc from order_status_lookup

Again the idea is to simplify.  Reduce the number of tables required to
represent a business model.

>>  - 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.

Now we have a master table,  a lookup table AND a view?
even more complication....

>> 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.

I would like to hear your argument on this.  I don't see how optimizing
the storage of reference value breaks a normalization rule.

--Dave


In response to

Responses

pgsql-hackers by date

Next:From: KevinDate: 2001-07-03 21:27:04
Subject: SNMP support
Previous:From: Tom LaneDate: 2001-07-03 21:17:53
Subject: Re: Help with SI buffer overflow error

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