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

问题描述

我有两个表，我想正常化。问题是我不想创建一个新的字段的表，虽然链接表可能工作。什么是最优雅的方式来表达任天堂条目是一个出版商和开发商？我不想要任天堂复制。我认为一个多对多的关系在这里可以是关键。

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.

这里是我想要规范化的两个表：

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屋！