Similar data, sharing tables

From: Harlan Messinger <zzzhmessinger_at_erols.com>
Date: Thu, 6 Sep 2001 18:30:55 -0400
Message-ID: <9n8t7p$t0d$1_at_bob.news.rcn.net>

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.

I could rename the PLAYER table PARTICIPANT, add a participantType column ("C" for coach", "P" for player), and add a university column. For each type of participant, I would leave NULL the inapplicable columns.

A variation: since there is one coach per team, I could put a coachUniversity column in the TEAM table in lieu of the university column in the PARTICIPANT table. Efficient, yes. Inelegant, though, it seems to me, and I wonder whether it's unadvisable, since the university does describe the coach, so it seems it should reside with the rest of the coach data. I would only consider this if it was guaranteed that a team has one coach. If multiple coaches were even conceivable, this would be an unacceptable approach.

I could have a separate COACHUNIVERSITY table with a participantID column that relates to the PARTICIPANT table, and a university column, and nothing else. For that matter, I could have a PLAYER table (not the original PLAYER table) and move the age, grade and position columns into it.

Or I could just keep the original PLAYER table, and add a COACH table with many redundant columns.

Or I could just add all the coach data to the TEAM table, as long as I am certain that a team will only have one coach (at a time, anyway).

Comments? What's the best way to handle this, and why?

Would the answer be different if the data for a coach were a subset of the data for a player or vice versa, rather than the situation presented above, where each entity has data not relevant to the other entity?

To what extent does the answer change depending on the proportion of non-common data to common data?

Furthermore, does the answer change if I'm starting from scratch and need a database with teams, players, and coaches right from the beginning, rather adding coaches to an existing team-and-player database? Received on Fri Sep 07 2001 - 00:30:55 CEST