Re: table design structure

From: David Portas <REMOVE_BEFORE_REPLYING_dportas_at_acm.org>
Date: 16 Dec 2005 07:24:22 -0800
Message-ID: <1134746662.700656.82580_at_g47g2000cwa.googlegroups.com>


doug.fulton_at_gmail.com wrote:

> (i am not a developer so excuse me if this obvious)
>
>
> i have data in the form
>
>
> <foriegn_key> ,<data1>, <data2>, (<data3>, <data4>)
> (<data3>, <data4>)
> (<data3>, <data4>)
> (<data3>, <data4>)
> (<data3>, <data4>)
> (<data3>, <data4>)
> (<data3>, <data4>)
>
>
>
> <foriegn_key> ,<data1>, <data2>, (<data3>, <data4>)
> (<data3>, <data4>)
> (<data3>, <data4>)
>
>
> <foriegn_key> ,<data1>, <data2>, (<data3>, <data4>)
> (<data3>, <data4>)
>
>
> ie the amount of (<data3>, <data4>) can be anywhere between 2 and 30.
> most of the reports etc will be generte from the data 3 column.
>
> what i am trying to figure is how to store these dataforms.
>
> i am have some exposure to oracle and i think a nested table per
> foriegn_key or varrays are to possible solutions but what i should use
>
>
> cheers
>
> Nug

Your table sketch doesn't really give enough information to answer you properly. Here is an example of how it might look in standard SQL.

CREATE TABLE table1 (foo_key /* my assumption because no key was specified */ INTEGER NOT NULL PRIMARY KEY, foreign_key INTEGER NOT NULL /* REFERENCES unspecified */, data1 INTEGER NOT NULL, data2 INTEGER NOT NULL) ; CREATE TABLE table2 (foo_key INTEGER NOT NULL REFERENCES table1 (foo_key), data3 INTEGER NOT NULL, data4 INTEGER NOT NULL, PRIMARY KEY /* my assumption */ (foo_key, data3, data4)) ;

-- 
David Portas 
SQL Server MVP 
--
Received on Fri Dec 16 2005 - 16:24:22 CET

Original text of this message