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 designing a baseball simulation program and I have run into a problem with designing the boxscore schema. The problem I have is that I want to track how many runs are scored in each inning. The way I do this in the actual program is to use a dynamic array that grows for each inning played.

For those unfamiliar with the game of baseball, games are usually nine innings long unless the game is tied still at the end of the 9th inning. Baseball games therefore, have an undetermined length which means I cannot design the database to only have 9 columns for the runs scored each inning (well technically 18 (9-innings * 2-teams). One idea I have had is to serialize the array and encode it as Base64 before storing it in the database. However, I do not know if this a good technique to use and I was wondering if anyone has a better idea.

In case it matters, the database I am developing around is PostgreSQL.

Any suggestions are greatly appreciated! Thanks!

share|improve this question

3 Answers 3

up vote 5 down vote accepted

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 REFERENCES [...],
    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 XML | TINYINT[] | VARBINARY | ETC., --Use to hold any runs in extra innings.
    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 IDENTITY PRIMARY KEY,
    GameId INT NOT NULL REFERENCES [...],
    Team CHAR(4) NOT NULL, --'Home','Away'
    InningId TINYINT, --Seeing how more than 255 innings might be excessive
    Runs TINYINT,
    UNIQUE (GameId, Team, InningId)
)

Edit: I know PostgreSQL uses Sequences instead of IDENTITY, I don't recall the correct syntax off hand, so translate accordingly.

share|improve this answer
    
haha, I like that I purposely didn't read your answer till I wrote mine and we're very close to the other. Nice. –  jcolebrand Feb 14 '11 at 15:37
    
Thank you for this answer, it makes sense and will be how I implement the box score schema. –  King Philroy Feb 15 '11 at 3:19

So what I'm seeing here is a little contradictory because innings aren't really directly an attribute of games, except indirectly. But maybe that's just me. I would personally suggest something more like a RunsScored table, and have it link back to a GamesHeader table, of some sort, so consider:

CREATE TABLE GamesHeader (
    GameID     INT IDENTITY(1,1),
    HomeTeamID INT,  --FK to teams table, naturally
    AwayTeamID INT,  --FK to teams table, naturally
    FinalInningsCount BYTE,  -- for faster reporting after the game is over
    FinalHomeScore BYTE,     -- for faster reporting after the game is over
    FinalAwayScore BYTE,     -- for faster reporting after the game is over
    --Other attribs
)

CREATE TABLE RunsScored (
    RunsScoredID BIGINT IDENTITY(1,1), -- for faster reverse traversal, possibly. May not be needed, this depends on your setup, as the normalization will show a composite key anyways
    PlayerID INT,   --FK to players table naturally
    GameID INT,     --FK to GamesHeader table naturally
    Inning BYTE, --wait for the payoff
    RunsEarned,     --because you may want to track this by the player ... really the problem is that there's not a single naturalized setup for this, so you may be intersecting this table to another stats table elsewhere. idk, it depends on your model. I'm going for fairly simplistic atm. Wanted to demonstrate something else entirely, but this needs to be accounted for.
     -- other attribs
)

SELECT MAX(r.Inning) FROM RunsScored r JOIN GamesHeader g ON g.GameID = r.GameID WHERE GameID = 'x'

That'll give you the maximum Inning played for a particular game, and you can further refine by PlayerID -> TeamID to figure out more details if you wanted to. What those might be I'm not sure.

I would probably actually refine that second table to not be RunsScored but something about AtBat because that's really what you're tracking. I just wanted to show how you could denormalize the inning away from the game table. I would tweak my model to flow like that, were this my project. HTH. YMMV.

Also note that I'm a TSQL guy, but I think the concepts expressed below work pretty well for explaining my concept. Language semantics probably won't line up.

share|improve this answer

I don't think there is anything wrong with just having a column

inning_score int[]

for 1 through 9 and beyond. That's one of the few places where using an array might be reasonable.

share|improve this answer

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.