Re: table design structure

doug.fulton_at_gmail.com wrote:
> 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>)
> what i am trying to figure is how to store these dataforms.

A classical factoring situation. I assume the key's are all different from each other (otherwise, there'd be no point calling them keys); otherwise, add an extra column that will provide a unique identifier.

Supposing the keys are, respectively, A1, A2, A3; the 2nd column items; B1,B2,B3; the 3rd column items C1,C2,C3; and the 4th & 5th column items, respectively, D1-D12, E1-E12; you're *factoring* the table into 2 tables linked by the key:

Table 1:
A1 B1 C1
A2 B2 C2
A3 B3 C3

Table 2:
A1 D1 E1
A1 D2 E2
A1 D3 E3
A1 D4 E4
A1 D5 E5
A1 D6 E6
A1 D7 E7
A2 D8 E8
A2 D9 E9
A2 D10 E10
A3 D11 E11
A3 D12 E12

It's the underlying query language that's supposed to link data (which is why you factor out the tables), not the tables themselves.

Example: To find all the D's & E's for anyone who has (B,C)'s equal to a specific value (8,9) (for instance) would entail a query (stated here in informal language) like this:

                  find all the (D,E)'s in Table 2
                  whose corresponding A entry matches
                  any A from Table 1 whose corresponding (B,C) = (8,9)

This can be readily framed in terms of a standard query language, like SQL, though I'll leave it for you to try out for yourself. Received on Fri Dec 16 2005 - 22:20:54 CET