How do I normalise this database design?

问题:

I am creating a rowing reporting and statistics system for a client where I have a structure at the moment similar to the following:

-----------------------------------------------------------------------------
| ID | Team     | Coaches    | Rowers     | Event     | Position | Time     |
-----------------------------------------------------------------------------
| 18 | TeamName | CoachName1 | RowerName1 | EventName | 1        | 01:32:34 |
|    |          | CoachName2 | RowerName2 |           |          |          |
|    |          |            | RowerName3 |           |          |          |
|    |          |            | RowerName4 |           |          |          |
-----------------------------------------------------------------------------

This is an example row of data but I would like to expand this out to a Rowers table and Coaches table and so on but I don't know how best to then link that back to the Entries table which is what this is.

Has anybody got any words of wisdom they could share with me?

Update

A Team can have any number of Coaches and Rowers, a Rower can be in many Teams (Team A, B, C etc) and a Team can have many Coaches.

回答1:

I would go with separate tables for Teams, Coaches, Rowers (maybe Athletes if there are more than one type of sport that you'll want to scale to) and Events.

Teams

TeamID

Coaches

CoachID

Rowers

RowerID

Events

EventID TeamID RowerID

There are a lot of other questions that will further define the relationships, like: What defines the entry (event)? Is it one set of rowers and one coach PER event? Multiple coaches per a set of rowers per event? Are there Teams? What makes up a team?

I'd be asking questions like that...

回答2:

A sort of link table? How would I then map multiple Rowers and Coaches to one Team?

This is my proposal (based on Optimal Solutions' answer):

Team
TeamID
TeamName

Coach
CoachID
CoachName

Rower
RowerID
RowerName

Event
EventID
Position
Time

1. if you want predefined teams:

CoachTeamLink (link between Coachs and Teams)
TeamID
CoachID

RowerTeamLink (link between Rowers and Teams)
TeamID
RowerID

EventTeamLink (link between Events and Teams)
TeamID
EventID

2. Or if you dont:

EventCoachLink (link between Events and Coachs)
EventID
CoachID

EventRowerLink (link between Events and Rowers)
EventID
RowerID

回答3:

You say what you posted is "the Entries table," but this table doesn't make much sense, and least not in terms of (one row, no pun intended) = (one fact or thing).

One of the rows in your table is (NULL,NULL,NULL,RowerName3,NULL,NULL,NULL). What does that mean? What is CoachName2 the coach for? And so on.

Or perhaps this is "one row" of your Entries table? If so, it's not a good SQL table, because you have lists of rowers (for example) in a single row-column intersection, and "list-of-rowers" isn't a good choice of data type.

It's impossible to say how to normalize a database if you only look at some data. You need to know what the data means and represents in the business it models.

That said, it looks to me like you at least need separate tables for

Teams Coaches Rowers Events

And then you need to represent aspects of the business scenario like "A rower belongs to exactly one team," "A team has at least one coach, and no coach coaches more than one team."

I don't know what an Entry is from looking at this, but perhaps you need a table for Entries, too. Maybe if I knew about rowing it would make more sense.

回答4:

I would have the following tables:

Team: ID, TeamName Coach: ID, CoachName, TeamID Rower: ID, RowerName, TeamID

Then your Event table would be populated as follows:

Event: ID, TeamID, CoachID, RowerID, EventName, Position, Time

If you wanted to display de-normalized data in a query, you would build something like:

SELECT E.ID, T.TeamName, C.CoachName, R.RowerName, E.EventName, E.Position, E.Time
FROM Event E
INNER JOIN Team T
ON E.TeamID = T.ID
INNER JOIN Coach C
ON E.CoachID = C.ID
INNER JOIN Rower R
ON E.RowerID = R.ID

回答5:

I'd try something like this::

teams
  id
  name


rowers
  id
  name
  team_id REFERENCES TEAMS (id)

coaches
  id
  name
  team_id REFERENCES teams (id)

events
  id
  name
  starting_time
  length

event_teams
  event_id references events (id)
  team_id references teams (id)
  position