1

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?

5

A reasonable solution is the second one, that is to remove the MakeId FK from Vehicles, for two reasons:

  1. The information about the Maker of a certain Model is already contained in the Model record, so storing it inside Vehicle is redundant.

  2. Having both attributes in Vehicle is prone to errors, since one could have inconsistent information inside a Vehicle.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.