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

问题描述

我有一个记分数据库的架构，其中包含 Game、Team、Player 表.

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 添加每个 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.

推荐答案

你的设计的问题是你使用了 代理标识符 作为表的主键，定义好的主键可以解决问题:

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 的总和.

So
player_score will be property of GamePlayer.
team_score will (may) be SUM of GamePlayer.player_score with specific team_id.

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