(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?
- Makes data entry and table maintenance easy.
- I can still query the table for a particular say 'Manufacturer' because of the foreign key reference.
- I can paste data structured in the same way from Excel directly into the table.
- And also, more importantly, many-to-many relationships are handled in this example.
- 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?