优雅的规范化，无需添加字段，额外的表.最好的关系 [英] Elegant normalization without adding fields, extra table. Best relationship

问题描述

我有 2 个表我正在尝试标准化.问题是我不想创建一个带有新字段的临时表，尽管链接表可能有效.传达Nintendo"条目既是发行商又是开发商的最优雅的方式是什么?我不希望任天堂"被复制.我认为多对多关系可能是这里的关键.

I have 2 tables I am trying to normalize. The problem is I don't want to create an offhand table with new fields, though a link table perhaps works. What is the most elegant way to convey that the "Nintendo" entry is BOTH a publisher and a developer? I don't want "Nintendo" to be duplicated. I am thinking a many-to-many relationship can be key here.

我想强调的是，我绝对希望保留开发者和发布者表.我不介意在两者之间建立一个新的关系.

I want to stress that I absolutely want the developer and a publisher tables to remain. I don't mind creating a link between the 2 with a new relationship.

这是我试图标准化的 2 个表:

Here are the 2 tables I am trying to normalize:

以下是我尝试过的解决方案(我不喜欢):

Below is a solution I tried (I don't like it):

推荐答案

我想你想要这样的:

Game_Company
ID    Name
 1    Retro Studios
 2    HAL Laboratories
 3    Nintendo
 ...

Company_Role
ID    Name
 1    Developer
 2    Publisher
 ...

Game_Company_Role
CompanyID    RoleID
        1         1
        2         1
        3         1
        3         2
 ...

要获取具有开发人员"角色的所有公司的列表:

To get a list of all companies that have role 'Developer':

SELECT gc.name
FROM Game_Company gc JOIN Game_Company_Role gcr ON gcr.CompanyID=gc.ID
WHERE gcr.RoleID = 1

这篇关于优雅的规范化，无需添加字段，额外的表.最好的关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！