Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Table Design Question

Re: Table Design Question

From: <Jared.Still_at_radisys.com>
Date: Thu, 5 Aug 2004 10:35:47 -0700
Message-ID: <OFD6FE5DEB.F01FC948-ON88256EE7.00603FF8-88256EE7.0060A98D@radisys.com>


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_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:32:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US