Re: db layout question

On Thu, 31 Jan 2002 09:17:47 +0100, Heinz Huber <hhuber_at_racon-linz.at> wrote:

>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

And something to enforce that a dog and a person cannot have the same FriendID ...

-- 
Nis Jorgensen
Amsterdam

Join the Patti Beadles Fan Club!
Details to follow.