MySQL Database Schema for Volleyball

Question

I am developing a database for the recording of Volleyball results. I have a fixture database that tells me who plays who in each round.

Teams Table, say IDs 1,2,3,4 Fixtures Table, Round No.1, Team 1 (Team.ID) v Team 2 (Team.ID)

Results is harder and I am stuck on the schema. Each Volleyball game is best of 5 sets, so it can be 3, 4 or 5 sets long. I need to record Fixtures.ID, Team1.Set1, Team1.Set2, Team1.Set3, Team1.Set4 and Team1.Set5 (and same for Team 2). Is this the best way of doing it, or should some normalisation process have an entry like Result.ID, Fixture.ID, Result.TeamID, Result.SetNo, Result.SetScore

Answer 1

Like Gaius said, review the answers at How-to implement an entity with an unknown maximum number of attributes?

Rewriting for MySQL Syntax from the answer by Eric Humphrey at above link:

You could do this. It would allow good performance for normal duration games, while allowing you to also store long running games.

CREATE TABLE InningRuns (
    GameId INT NOT NULL AUTO_INCREMENT,
    Team CHAR(4) NOT NULL, --'Home','Away'
    Inning1 TINYINT, --Seeing how more than 255 runs are not really possible in an inning
    Inning2 TINYINT,
    [...],
    Inning9 TINYINT,
    ExtraInnings VARCHAR(2000), --Use to hold any runs in extra innings, store multiple values using some delimiter like comma (,), pipe (|), semicolon (;), or anything that is not part of the data.
    PRIMARY KEY (GameId, Team)
)

You could further normalize and have a row for each unique combination of game, team, & inning. This would allow you as many innings as the InningId datatype would allow.

CREATE TABLE InningRuns (
    InningRunId INT NOT NULL AUTO_INCREMENT,
    GameId INT NOT NULL,
    Team CHAR(4) NOT NULL, --'Home','Away'
    InningId TINYINT, --Seeing how more than 255 innings might be excessive
    Runs TINYINT,
    PRIMARY KEY (InningRunId),
    UNIQUE (GameId, Team, InningId)
)