RE: Table Design Question

From: Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us>
Date: Thu, 5 Aug 2004 13:25:09 -0400
Message-ID: <DE8A21F8F1D0254EA4A9214D52AB2FED11F2C03A@exchsen0a1ma>

Pallav,

You have it backward. If I read it right, the Listing table is the parent table, with the Parameter table being the child. So it should be like this:

Table1 -> Listing

Columns      Datatype 
Listingid     Integer  (Pk)

Table2 -> Parameter
Columns      Datatype
Listingid     Integer  (Pk,Fk)
Parameterid    Integer  (Pk)
StringVal      Varchar
IntegerVal     Integer
MoneyVal       Money
DateVal        Date

As for your question regarding nulls, I don't think it matters. You could have just one varchar2 field in the Parameter table, along with a "datatypecode" field that indicates whether the values is a char, interger, money or date column, but I'm not sure what that buys you.

What exactly are these tables for? Looks like documentation for your database tables??

Hope this helps.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----

From: Pallav Kalva [mailto:pallav_oracle_at_yahoo.com] Sent: Thursday, August 05, 2004 1:05 PM
To: oracle-l_at_freelists.org
Subject: Table Design Question

Hi ,

I am working on an application and need some help designing these 2 tables,

Table1 -> Listing

Columns      Datatype 
Listingid     Integer  (Pk)

Parameterid Integer (Fk)

Table2 -> Parameter
Columns Datatype
Parameterid Integer (Pk)

StringVal      Varchar
IntegerVal     Integer
MoneyVal       Money
DateVal        Date

     Each ListingId will have lot of parameters and

all the parameters may not have all the values for each listing and also there might be more parameters in the future. Based on this we have decided to go with these 2 tables instead of having all the parameter column names in the Listing table. But if we go with this design in the parameter table we need to store the values of each parameter which can be of any data type, we might need to query on these values, so we thought of having separate columns for each datatype and there will be NULL values if that datatype doesnt correspond to the parameter value, so at any point there will 3 NULLS store for each entry in Parameter table.

Is this design optimal for performance ? I am hoping this would be bad on performance of queries on Parameter table. Is there an better way of achieving this ?

TIA,
Pallav

Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/

FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com


----------------------------------------------------------------

To unsubscribe send email to:  oracle-l-request_at_freelists.org

put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Thu Aug 05 2004 - 12:23:20 CDT