Search code examples
databasedatabase-designdatabase-normalization

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:

enter image description here

Below is a solution I tried (I don't like it): enter image description here

Solution

  • I think you want something like this:

    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