RE: Table Design Question

From: Looney, Jason <Jason.Looney_at_echostar.com>
Date: Thu, 5 Aug 2004 11:29:41 -0600
Message-ID: <B8C9CF34B3F7164282C8610C93BB94AF04C631CF@riv-exchb1.echostar.com>

Technically, what you have here is a Many to Many relationship that resolves into a Super-Type/Sub-Type table. What you need to do here is add an additional table.

LISTING
* LISTING_ID INTEGER

SALES_AGENT (etc)

PARAMETER
* PARAMETER_ID INTEGER

PARAMTER_TYPE VARCHAR2(20) -- This would be 'DATE', 'MONEY', 'STRING' DESCRIPTION VARCHAR2(200) LISTING_PARAMTER
* LISTING_ID INTEGER

PARAMETER_ID INTEGER VALUE VARCHAR2(2000)

Every design has pros and cons so here's the list:

PROS:

This will ensure that you keep the minimum amount of data.
By storing the type as a parseable string you can add without impact.
It is easy to read.

CONS:

You won't be able to SUM up the VALUE column.
To determine the PARAMETER_TYPE you will need to join. You could denormalize this and carry it down to the LISTING_PARAMTER table to save the join.
You have to parse the VALUE string every time you need the data.

An alternate design, which would be acceptable, is to have the LISTING_PARAMETER table have the columns STRING_VALUE, NUMBER_VALUE, DATE_VALUE. Then you don't need to carry the parameter_type column down and it would be SUMable. This variation is probably how I would do it.

If you have any other questions, let me know.

Jason.

-----Original Message-----
From: Pallav Kalva [mailto:pallav_oracle_at_yahoo.com] Sent: Thursday, August 05, 2004 11:05 AM 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:29:16 CDT