这个规范化是否正确？ （两个多对一连接由多对一） [英] Is this normalization correct? (two many-to-manys connected by a many-to-one)

问题描述

我有一个记分数据库的模式，游戏，团队， / code>表。

I have a schema for a scorekeeping database with Game, Team, Player tables.

一个团队有很多玩家，每个玩家只有一个团队。每个团队玩很多游戏，每个游戏有很多团队。在每个游戏中，玩家以一个团队的形式分成一定数量的点 - 这映射到 player_score 和 team_score 。一个队伍的总分数是所有球员的总和 player_score 对于该游戏和团队的 team_score

One team has many players, each player has only one team. Each team plays many games, each game has many teams. In each game, players score a certain number points individually and as a team - this maps to a player_score and a team_score. A team's total score for a game is the sum of all of its players player_score for that game and the team's team_score for that game.

这是我的计划 -

GameTeam 表包括该游戏的团队 team_score ，并且外键 Game.id 和 Team.id 。很多很多。

GameTeam table includes the team's team_score for that game, and has foreign keys of Game.id and Team.id. Many to many.

GamePlayer 表包括玩家 player_score 对于该游戏，并且具有 Game.id 和 Player.id 的外键。很多很多。

GamePlayer table includes the player's player_score for that game, and has foreign keys of Game.id and Player.id. Many to many.

所以问题是 GameTeam 和 GamePlayer 没有链接，它似乎应该是 - 因为一个球员总是属于一个团队。我的解决方案是在 GameTeam 和 GamePlayer 之间添加一对多关系，那么如果我有一个游戏id和一个团队id我可以搜索一个 GameTeam 其中匹配，迭代所有 gameTeam.gamePlayers code> player_score ，在最后添加 team_score ，并计算 total_score 。

So the problem is that GameTeam and GamePlayer aren't linked and it seems like they should be - since a player always belongs to one team. My solution was to add a one-to-many relationship between GameTeam and GamePlayer, then if I have a game id and a team id I can search for a GameTeam where those match, iterate over all the gameTeam.gamePlayers adding each player_score, add on the team_score at the end, and calculate total_score.

这是否有意义？我完全关闭？任何帮助感谢，谢谢。如果重要，我使用SQLAlchemy。

Does this make sense? Am I completely off? Any help appreciated, thanks. If it matters, I'm using SQLAlchemy.

推荐答案

您的设计的问题是， http://en.wikipedia.org/wiki/Surrogate_keyrel =nofollow>代理标识符作为表的主键，明确定义的主键将解决问题：

The problem to your design is that you have used surrogate identifier as the primary key for the tables, a well defined primary key will solve the problem:

Team   -> pk:team_id
Player -> pk:player_id
TeamPlayer -> pk:{team_id + player_id}

Game   -> pk:game_id
GameTeam -> pk:{game_id + team_id}
GamePlayer -> pk:{game_id + GameTeam_pk + TeamPlayer_pk} 
              = {game_id + {game_id + team_id} + {team_id + player_id} }

对 GamePlayer 进行检查约束会有助于解决问题：

Having check constraints on GamePlayer will help the problem:

GamePlayer 
{
 Check game_id (FK of Game) = game_id (FK of GameTeam );
 Check team_id (FK of GameTeam) = team_id (FK of TeamPlayer);
}

所以
player_score将是 GamePlayer的属性。

team_score将（可能）是具有特定team_id的 GamePlayer.player_score 的总和。

这篇关于这个规范化是否正确？ （两个多对一连接由多对一）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！