How to construct a Junction Table for Many-to-Many relationship without breaking Normal Form

2.5k views Asked by At

I have these two tables, Company and Owner. Right now they are both in Normal Form, but I need to create a Many-to-Many relationship between them, since one Company can have many Owners and one Owner can have many Companies.

I have previously gotten an answer to whether adding an array of CompanyOwners (with Owner UUIDs) to Companies would break Normal Form, It will break Normal Form, and have been able to gather that what could be used is a Junction Table, see thread.

My question is as following: will the creation of an additional Junction Table as shown below, break Normal Form?

-- This is the junction table.
CREATE TABLE CompanyOwners(
    Connection-ID UUID NOT NULL, // Just the ID (PK) of the relationship.
    Company-ID UUID NOT NULL REFERENCES Company (Company-ID),
    Owner-ID UUID NOT NULL REFERENCES Owner (Owner-ID),
    CONSTRAINT "CompanyOwners" PRIMARY KEY ("Connection-ID")
)
2

There are 2 answers

2
Mike Sherrill 'Cat Recall' On BEST ANSWER

Your structure allows duplicate data. For example, it allows data like this. (UUIDs abbreviated to prevent horizontal scrolling.)

Connection_id            Company_id               Owner_id
--
b56f5dc4...af5762ad2f86  4d34cd58...a4a529eefd65  3737dd70...a359346a13b3
0778038c...ad9525bd6099  4d34cd58...a4a529eefd65  3737dd70...a359346a13b3
8632c51e...1876f6d2ebd7  4d34cd58...a4a529eefd65  3737dd70...a359346a13b3

Each row in a relation should have a distinct meaning. This table allows millions of rows that mean the same thing.

Something along these lines is better. It's in 5NF.

CREATE TABLE CompanyOwners(
    Company_ID UUID NOT NULL references Company (Company_ID),
    Owner_ID UUID NOT NULL references Owner (Owner_ID),
    PRIMARY KEY (Company_ID, Owner_ID)
);

Standard SQL doesn't allow "-" in identifiers.

0
M.Ali On

This is fine as it is but you could add a couple of more columns like

DateOwned Datetime  --<-- when the owner bought the company 
DateSold Datetime  --<-- when a the owner sold the compnay

After all you would want to know something like is company is still owned by the same owner, and keep track of the company's ownership history etc.