I am designing tables for tracking statistics for multiple sports like basketball and football. So my question now is - would it make more sense to

  1. design a table when both data for basketball and football are stored
  2. separate the stats for basketball and football completely
  3. create a base table and store common information there and then split them into 2 different tables where they start to differ from each other

my main concern with option 1 is that down the road, the table might become too complex and too many optional/nullable columns

number 2 might be overkill? i like reusing things whenever i can. number 3 - not sure how this would look like...

  • Number 3 sounds like a pattern called class table inhertance. Search on that. – Walter Mitty Oct 29 '17 at 18:21

If you find yourself saying "nullable columns" before you have written code I generally think you need to normalize more(as always, it depends.)

If you want one system to maintain, the last option seems the most relevant, but "splitting them into two tables" is not really where you would want to take it.

if we want to track basic things like Players, Games, and Sport type you would simply add intersection tables between the relevant things you want to store.

Players(playerid, player information) -> PlayerGames(playerid, gameid) <- Games (gameid, game information) ^ | PlayerSport (playerid, sportid) | v Sports (sportid, sportname)

This means we could count all of Michael Jordan's games by sport(as he played multiple) with a query something like:

select count(*), sportname from Players p join PlayerSport ps ON ps.playerid = p.playerid join PlayerGames pg ON p.playerid = pg.playerid where p.name='Michael Jordan' group by sportname

| improve this answer | |

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