I'll start of with an example database design to give you an idea what I'm currently dealing with.
I have a table which holds all the makes of vehicles and another table which holds the models for each make. This would result in the following db design:
Makes
- Id
- Name
Models
- Id
- Name
- MakeId
So far it's pretty straight forward, but now comes that somewhat hard part which introduces the 'unavoidable' circular reference in my scenario. I'm introducing another table named Vehicles, which is used to configure a particular vehicle.
Vehicles
- Id
- MakeId
- ModelId
- Variant (for example 3.0 TDI, just a plain text field).
The reason I came to this design is because I have a frontend web application which allows the user to fill in a form to configure a Vehicle. Inside the form the user gets presented with two select boxes, one for the make and one for the model. In this case the models belonging to a make are dynamically loaded once a make has been selected by the user, hence the FK relationship between Models and Makes.
Possible solutions:
1) Remove the FK relationships from the Vehicles table and just store the make name and model name, but if for some reason these values change, already inserted Vehicle records won't show the updated name.
2) Store only the ModelId in Vehicles
3) Create a many-to-many table between Vehicles and Makes and Models and store the Id of the many-to-many table as the FK in the Vehicles table. This however would require the frontend to perform a lookup based on MakeId and ModelId to get the id of the many-to-many table.
Which approach is recommended? Are there better alternatives?
