|
This type of structure, in which one table is related to another through
multiple IDs or keys that are stored within a single column, usually in a
comma-separated list, can easily get you into trouble. For example,
Customer
id name accounts
71 J.Smith 12
72 B.White 14,24
73 K.Baker 16,20,26
75 A.Brown 22
Account
id type amount
12 bonds 1047.00
14 stock 937.21
16 bonds 3700.00
20 cash 42.98
22 stock 10456.34
24 cash 211.03
26 stock 2044.65
So B.White has two accounts,
a stock account for 937.21, and a cash account for 211.03.
It is easy enough to see how someone can come up
with this design. After all, it's called a one-to-many
relationship, and sure enough, each customer has one
or more accounts. So at first blush, it does indeed
look like a one-to-many relationship.
But, alas, the foreign keys are in the wrong table.
The way to relate tables in a one-to-many relationship
is to remember that:
So the preferred design is:
Customer
id name
71 J.Smith
72 B.White
73 K.Baker
75 A.Brown
Account
id custid type amount
12 71 bonds 1047.00
14 72 stock 937.21
16 73 bonds 3700.00
20 73 cash 42.98
22 75 stock 10456.34
24 72 cash 211.03
26 73 stock 2044.65
Space does not permit a full explanation of
all the reasons why the comma-separated list of IDs
is a poor design. The main problem is
that it makes table joins difficult, if not impossible. For More Information
|
