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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin | 2001-07-03 21:27:04 | SNMP support |
Previous Message | Tom Lane | 2001-07-03 21:17:53 | Re: Help with SI buffer overflow error |