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

  • 1
    See this question
    – Gaius
    Feb 16, 2011 at 12:28
  • 1
    I agree with Gaius, these are all going to fit the same general conceptual model. You have to disambiguate rounds from games, they can't be associated as a 1:1 relationship. Let us know if that helps you and we'll close this one as a duplicate.
    – jcolebrand
    Feb 16, 2011 at 15:01
  • Thanks. I got lost with the basketball example, mainly as I don't understand the postgres terms and can't relate it well to mysql.
    – Vicsig
    Feb 20, 2011 at 21:48
  • @Vicsig ~ Sorry you got lost in the shuffle, but welcome to SO. In the future, to get someone's attention who has commented on your post, you should use @drachenstern (to flag me for instance ~ the general format is @ and their username ~ up to the first three letters minimum) and then your message. That would get people to come back pretty quick ;)
    – jcolebrand
    Mar 3, 2011 at 2:12
  • @vicsig ~ which part of that one is giving you grief? Did you see my example towards the bottom? It works the same way. Essentially, you want to divest that there is a "set" number of "rounds" from the schema, as those are not in actuality set, due to overtime or additional rounds. So you need a table that has one entry per round, or something to that effect. It's easier to leave round number to be a freely associated field rather than a part of the schema.
    – jcolebrand
    Mar 3, 2011 at 2:14

1 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.

    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.

    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)
  • I like your initiative on this one !!! +1 Jul 12, 2011 at 19:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.