RE: Table Design Question
- From: "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 5 Aug 2004 13:25:09 -0400
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@xxxxxxxxx]
Sent: Thursday, August 05, 2004 1:05 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx put
'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
