Database Design – Use Only One Junction Table per Database?

What does your colleague propose as the primary key for this link table?
Primary key columns can not be NULL of course: the table above has nullable.

There isn't any natural row identifier (which is what a PK is) in the example above (a IDENTITY column is not a primary key), therefore it fails in any modelling process. Don't even think about creating tables without some model (ERD, ORM, IDEF1X, whatever)

You'd also need CHECK constraints to ensure you don't have 3 way links.

Finally, you're straying into 4th and 5th normal form territory but for the wrong reasons.

I can't find any examples on the internet: that shows how stupid this is

There is nothing wrong (in principle) with a three way (or more) intersection table, as long as it properly describes your relation.

However: Your particular table is not in third normal form (3NF).

You should generally normalize to 3NF unless you have good reason not to.

The problem is that some of your data depends on part of the key, instead of on the whole key. This means your table is only in second normal form (2NF).

Consider this: Your table would allow a record that says a Prius is a Toyota and another record that says a Prius is a Chrysler. Obviously that's not good.

You want to remove the hierarchy of make and model into a separate table.

Similarly, the type of a car is dependent on the model, but not on the colour.

So what you need to do is separate these columns too. What you really need are four tables:

• Manufacturers (manufacturer name)

• Models (model name, FK to Types and FK to Manufacturers)

• Types (type name)

• Cars (FK to Models, colour)

This would give you a normalized schema with no redundancy and very little risk of data corruption due to insert, update and delete anomalies.