Re: Similar data, sharing tables
Date: Fri, 21 Sep 2001 08:24:34 +0200
Message-ID: <3BAADD22.B7F8F922_at_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 - 08:24:34 CEST