How to design a sports statistics database for multiple sports? [closed]_问答_开发者

Closed. This question needs to be more focused. It is not currently accepting answers.

Want to improve this question? Update the question so it focuses on one problem only by editing this post.

Closed 3 years ago.

Improve this question

I'm trying to design a database to store player statistics for m开发者_StackOverflow中文版ultiple types of sports.

In the database, you would have a bunch of players. Each player belongs to one team, and each team plays one sport. Here are the tables I have for just that part so far:

Player (Player_ID, Team_ID, FirstName, LastName)

Team (Team_ID, Sport_ID, TeamName)

Sport (Sport_ID, SportName)

Now I want to store the player's statistics. I run into a problem with different players playing different sports and therefore having to store different kinds of statistics for each sport. If I were storing hockey players and baseball players for example:

for the hockey players I want to store goals and assists
for the baseball players I want to store hits, home runs and RBIs.

Is there a proper way to do something like this using one database? Have I even started the right way?

You can get inspiration from sportsdb, a rdbms schema for sports modeling created by XML Team. In that schema the game and players statistics are stored in denormalized tables, and the core stats (like score) are kept separated from the sports-specific ones. The schema is quite complex, but can give you an idea about a possible implementation

The proper way is to make multiple tables, one for each sport seems likely.

Player (Player_ID, FirstName, LastName)
Team (Team_ID, Sport_ID, TeamName)
TeamList (Team_ID, Player_ID)
Sport (Sport_ID, SportName)
HockeyStats (Player_ID, Team_ID, Year, Goals, GamesPlayed, Assists)
BaseBallStats (Player_ID, Team_ID, Years, BoringSport)

This also resolves the situation with trading, which team the points were obtained from, as well as multiple sports.

An alternative approach is a single table with a statistic-ID column, identifying which statistic is being described in that row. This may work well if the statistics to be kept will change over time. It will only work well if the basic form of each statistic is the same (sorry, I'm British and not familiar with the sports and stats you mention), but there are multi-table minor variants (all percentage stats in one table, all simple-count stats in another, etc).

Your initial design is ok.

You will need to create a table for games. So you would have a games table with fields (Game_ID, Sport_ID,Team1_ID, Team2_ID) and then maybe (Date_Played, Win_Lose_Tie etc...)

I would then place Create a Statistics Table for each sport. TBL_Baseball_Stats (Stat_ID, Game_ID, Player_ID, Hits, HRs, RBIs etc...

Then you could have a table for Hockey Stats. TBL_Hockey_Stats (Stat_ID,Game_ID, Player_ID, Goals, Assists, etc ...)

This will allow you to pull up stats by Player, Game, Sport, etc...

If you want to be able to dynamically create new sports, without modifying the database at all, you will have to create something smart enough to generalize the storing of any kind of statistics. Whatever is your choice, you won't be able to do that with a single table.

So if you have a determined number of different sports, I would recommend you to create one table per statistic.

If not (you want to be able to create new sports in the future with their statistics), you should look for how to abstract such a thing. Some draft toughts:

a sport has many stats
a stat has many fields and one sport
a field has many properties (type, value, etc.)

If the same outlines appear in different statistics, you may take advantage of table inheritance.

Multiple sport have it's own different type of rule so you can use use document based database(Eg: MONGODB) because it is flexible and not have specific column.

you must note that there are certain athletes that have the same name (first and last) therefor take that under consideration while defining primary keys.

For baseball there are a lot of different type of stats- i would try to meet the expectations to competing DBs by at least adding AVG. /Games/ Runs/Hits/ AB/ SB/ 2B/ 3B. and pitcher stats as well- W/ERA/WHIP/SO.