db layout question

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?

Thanks
Alex Tomlinson Received on Wed Jan 30 2002 - 22:31:13 CST