您可以从 sportsdb 中获得灵感，这是 XML Team 创建的用于运动建模的 RDBMS 模式。在该模式中，比赛和玩家统计数据存储在非规范化表中，并且核心统计数据（如得分）与特定运动的统计数据分开。该模式非常复杂，但可以让您了解可能的实现
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
另一种方法是使用带有统计 ID 列的单个表，用于标识该行中描述的统计信息。如果要保存的统计数据会随着时间的推移而改变，这可能会很有效。只有当每个统计数据的基本形式相同时，它才会很好地工作（抱歉，我是英国人，不熟悉你提到的体育和统计数据），但是有多个表的小变体（所有百分比统计数据都在一个表中，另一个中的所有简单计数统计数据，等等）。
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).
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.
发布评论
评论（7）
您可以从 sportsdb 中获得灵感，这是 XML Team 创建的用于运动建模的 RDBMS 模式。在该模式中，比赛和玩家统计数据存储在非规范化表中，并且核心统计数据（如得分）与特定运动的统计数据分开。该模式非常复杂，但可以让您了解可能的实现
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
正确的方法是制作多个表格，每项运动似乎都有可能。
玩家（玩家 ID、名字、姓氏）
球队（Team_ID、Sport_ID、TeamName）
队伍列表（Team_ID、Player_ID）
运动（Sport_ID、SportName）
曲棍球统计（球员 ID、球队 ID、年份、进球数、已玩比赛数、助攻数）
BaseBallStats (Player_ID, Team_ID, Years, BoringSport)
这也解决了交易的情况，分数是从哪支球队获得的，以及多种运动。
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.
另一种方法是使用带有统计 ID 列的单个表，用于标识该行中描述的统计信息。如果要保存的统计数据会随着时间的推移而改变，这可能会很有效。只有当每个统计数据的基本形式相同时，它才会很好地工作（抱歉，我是英国人，不熟悉你提到的体育和统计数据），但是有多个表的小变体（所有百分比统计数据都在一个表中，另一个中的所有简单计数统计数据，等等）。
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).
你的初始设计没问题。
您需要创建一个游戏桌。因此，您将有一个包含字段（Game_ID、Sport_ID、Team1_ID、Team2_ID）的游戏表，然后可能是（Date_Played、Win_Lose_Tie 等...），
然后我将为每项运动创建一个统计表。 TBL_Baseball_Stats (Stat_ID, Game_ID, Player_ID, Hits, HRs, RBIs 等...
然后你可以有一个曲棍球统计表。 TBL_Hockey_Stats (Stat_ID,Game_ID, Player_ID, Goals, Assists, 等等...)
这将允许你拉按球员、比赛、运动等统计统计数据...
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:
If the same outlines appear in different statistics, you may take advantage of table inheritance.
多项运动都有自己不同类型的规则，因此您可以使用基于文档的数据库（例如：MONGODB），因为它很灵活并且没有特定的列。
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.
您必须注意，某些运动员具有相同的名字（名字和姓氏），因此在定义主键时要考虑到这一点。
对于棒球来说，有很多不同类型的统计数据 - 我会尝试至少通过添加 AVG 来满足竞争 DB 的期望。 /比赛/ 跑数/安打/ AB/ SB/ 2B/ 3B。以及投手统计数据 - W/ERA/WHIP/SO。
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.