How-to implement an entity with an unknown maximum number of attributes?

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!

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.

StackExchange Database Administrators Q#1206, answer score: 7