Are 2+ Foreign Keys a Bad Idea in any Association / Junction table

database-design

(First time poster, very very long time lurker. I cannot find an answer to this question anywhere, so I now come out from the shadows to ask the experts).

Is a junction table with say 4 foreign keys (4 tables), a good idea for tables that are only to be Read as a Lookup and not changed regularly, and which contain static data and would only be changed by an admin and not a 'user'?

I'll give a simple example.

Say we have four tables each has only two fields – an ID number field (Primary Key) and a 'Name' (text) field: tblCarModelName, tblCarType, tblManufacturer, tblColor.

And then we have a junction table (tblCars) which is designed as follows:

Car_ID (PK)
CarModelName_IDFK (FK)
CarType_IDFK (FK)
Manufacturer_IDFK (FK)
Color_IDFK (FK)

The table could look like (please note the IDFKs will be integers not strings as illustrated here):

Car_ID, CarModelName_IDFK, CarType_IDFK, Manufacturer_IDFK, Color_IDFK
1,Hilux,Truck,Toyota,White
2,SLK300,Sedan,Mercedes,Blue
3,SLK500,Sedan,Mercedes,Silver
4,Prius,Coupe,Toyota,White
....

So the table ends up looking like a spreadsheet. But why would I want to do this?

  1. Makes data entry and table maintenance easy.
  2. I can still query the table for a particular say 'Manufacturer'
    because of the foreign key reference.
  3. I can paste data structured in the same way from Excel directly into
    the table.
  4. And also, more importantly, many-to-many relationships are
    handled in this example.
  5. If I offer this table the exact same way in a user interface form, I
    know when the user chooses from this list, say ID = 3 and that they
    have chosen "SLK500,Sedan,Mercedes,Silver" from the list.

Is this design ideal? Feedback is appreciated.

What is this model called anyway?

Best Answer

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.

Related Question