Re: db layout question
Date: Thu, 31 Jan 2002 09:17:47 +0100
Message-ID: <3C58FDAB.8010501_at_racon-linz.at>
Alex Tomlinson wrote:
> What is the best way to represent a relationship where
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:
> 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?
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 - 09:17:47 CET