Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
See this question –  Gaius Feb 16 '11 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 '11 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 '11 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 '11 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 '11 at 2:14
show 1 more comment

1 Answer

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)
)
share|improve this answer
    
I like your initiative on this one !!! +1 –  RolandoMySQLDBA Jul 12 '11 at 19:51
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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