Re: db layout question

Alex Tomlinson wrote:

> What is the best way to represent a relationship where
> an item of type A is associated with exactly one item
> of type B or C or D. For example, consider a "best
> friend" relationship and suppose a person's best friend
> could be another person, a dog, or a computer.
>
> My inclination is to start with three tables (pardon my
> attempt at sql):
>
> CREATE TABLE Person(
> id INTEGER NOT NULL PRIMARY KEY,
> name CHAR(10) NOT NULL);
>
> CREATE TABLE Computer(
> id INTEGER NOT NULL PRIMARY KEY,
> brand CHAR(10) NOT NULL,
> speed INTEGER NOT NULL);
>
> CREATE TABLE Dog(
> id INTEGER NOT NULL PRIMARY KEY,
> name CHAR(10) NOT NULL,
> breed CHAR(10) NOT NULL);
>
> What's the best way to augment this to include the
> "best-friend" relation?

The best way is to introduce a "super" table that contains possible friends:

CREATE TABLE Friend(

     friendId INTEGER NOT NULL PRIMARY KEY );

Add the following to all of the three tables above and create a foreign key relationship:
friendId INTEGER NOT NULL

Alternatively, you could use the friendId as a PK for the three tables.

Add a column to Person referencing Friend to specify the best friend.

hth,
Heinz Received on Thu Jan 31 2002 - 02:17:47 CST