Re: Similar data, sharing tables

From: Heinz Huber <hhuber_at_racon-linz.at>
Date: Fri, 21 Sep 2001 08:24:34 +0200
Message-ID: <3BAADD22.B7F8F922@racon-linz.at>

Harlan Messinger wrote:
>
> Suppose I've got the following tables for secondary school athletic teams:
>
> TEAM
> teamID
> teamName
> organizationName
> address
> city
> state
> zip
> phone
>
> PLAYER
> playerID
> teamID
> playerName
> nickName
> address
> city
> state
> zip
> phone
> age
> grade
> position
>
> Now suppose I need to start storing information about each team's coach.
> The information required for a coach is *similar* to the information
> required for a player. But I don't need a coach's age, and grade and
> position are not applicable. On the other hand, I want the name of the
> coach's university.

[snip]

I'd do it that way:

PERSON
    personID PK
    personName
    nickName
    address
    city
    state
    zip
    phone

PLAYER

    personID           PK, FK
    teamID             PK, FK

    age
    grade
    position

COACH

    teamID             PK, FK
    personID           FK

    university

The question is what to make the PK in COACH table. A team can only have one coach, so that seems to be the right column.

If you can pull university into the PERSON table, you could omit the COACH table completely and add a personIDCoach to TEAM.

Since you can change the structure of the database (as you have pointed out in you mail later), the change should be possible. The migration of the data from PLAYER to PERSON should be pretty straight forward.

hth,
Heinz Received on Fri Sep 21 2001 - 01:24:34 CDT