多事件竞争的数据库设计模式，第2步将在多年内跟踪相同的数据

I am new to databases and I can't wrap my head around the schema I need.

我是数据库的新手，我无法围绕我需要的架构。

I have a table for competitors, and a table for events. Each competitor will compete in each of the 8 events. I believe I need a bridge table, and I have looked at the student/class model. I believe my problem lies in the fact that I am trying to place each of the 10 scores inside the event table, but I don't know how to make the association between them.

我有竞争对手的桌子和事件表。每个参赛者将参加8场比赛。我相信我需要一个桥牌表，而且我看过学生/班级模型。我相信我的问题在于我试图将10个分数中的每一个放在事件表中，但我不知道如何在它们之间建立关联。

Here is where I am at with my model: Click here for schema

以下是我使用模型的地方：单击此处查看模式

1 个解决方案

#1

From what you describe, the Score1 to Score10 are pieces of information that relate to a specific competitor competing in an event, so those would rightfully belong in the bridge table.

根据您的描述，Score1到Score10是与在某个事件中竞争的特定竞争者相关的信息，因此这些信息恰好属于桥接表。

You could also choose to have each score as a separate row in that bridge table, or to keep them all on a single row. You might choose to do the latter if you know that there will always be ten scores each time a competitor competes in an event.

您还可以选择将每个分数作为该桥表中的单独行，或者将它们全部保留在一行中。如果您知道每次竞争对手参加某项赛事时总会有十个分数，您可以选择执行后者。

So, your bridge table might look like this:

因此，您的桥表可能如下所示：

CREATE TABLE EventCompetitor (
    EventID int NOT NULL,
    CompetitorID int NOT NULL,
    Round int NOT NULL,
    Score int NULL,
    CONSTRAINT fEventID_EventCompetitor FOREIGN KEY (EventID) REFERENCES Event(EventID),
    CONSTRAINT fCompetitorID_EventCompetitor FOREIGN KEY (CompetitorID) REFERENCES Competitor(CompetitorID),
    CONSTRAINT pEventID_CompetitorID_Round_EventCompetitor PRIMARY KEY CLUSTERED (EventID, CompetitorID, Round));

So this way there would be ten rows to represent the ten scores that a competitor gets in the event and to find out the final score you just add all the scores:

所以这种方式会有十行代表竞争对手在比赛中得到的十个分数，并找出最终分数，你只需添加所有分数：

SELECT EventID, CompetitorID, TotalScore = SUM(Score)
  FROM EventCompetitor;

The other option would be for the bridge table to look like this:

另一个选项是桥表看起来像这样：

CREATE TABLE EventCompetitor (
    EventID int NOT NULL,
    CompetitorID int NOT NULL,
    Score01 int NULL,
    Score02 int NULL,
    Score03 int NULL,
    ...
    CONSTRAINT pEventID_CompetitorID_EventCompetitor PRIMARY KEY CLUSTERED (EventID, CompetitorID));

And your query to get the total score would be like this:

您获得总分的查询将如下所示：

SELECT EventID, CompetitorID,
       TotalScore = ISNULL(Score01, 0) + ISNULL(Score02, 0) + ISNULL(Score03, 0) + ...
  FROM EventCompetitor;

#1