Re: Table Design Question
- From: Jared.Still@xxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 5 Aug 2004 10:35:47 -0700
Comments inline:
>
> 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.
You're already at least one table short. You are describing
a many-to-many relation between Listing and Parameter, which
requires a third table to be used as an intersection table.
This could be referred to as an 'associative' entity if it
appeared on an ERD.
The intersection table would have as columns the PK columns
of both the Parameter and Listing tables. ( does this imply
that it leans to the left, or the right? :)
> 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.
Will the number of data types possible change in the future?
Then use another table for these.
> 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 ?
>
A join on 3 or 4 tables will be quite fast.
If you wish to denormalize for performance, just use flat files
and sed/grep/awk on a Sun E15k, HP Superdome, etc.
Jared
----------------------------------------------------------------
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:
