Table Design Question

From: Pallav Kalva <pallav_oracle_at_yahoo.com>
Date: Thu, 5 Aug 2004 10:04:45 -0700 (PDT)
Message-ID: <20040805170445.78340.qmail@web53410.mail.yahoo.com>

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

---

Received on Thu Aug 05 2004 - 12:01:36 CDT