Ensuring 1 to 1 mapping through a many to many table

database-design

I've currently got a database with two tables, "Product" and "Option". Each parameter is tied to one product. We want to extend this data with a new concept of a "Project". A product will have many projects and each project will list what the default values for each option are for that product. The trouble I'm having is thinking how to ensure that each project lists every option for the product it's tied to.

E.g. if a product has options A, B, and C. Each project needs to define the default value for A, B, and C. Adding a new option should require updating all the projects.

Any pointers?

Best Answer

First: you need somewhere a link table that stores a reference to both every product (or project) and its options' values. Let's call that one ProjectOptions, and let's assume it's structured like (ProjectId, OptionId, ValueForOption).

The trouble I'm having is thinking how to ensure that each project lists every option for the product it's tied to.

You can have an ON INSERT TRIGGER on table project that, whenever you insert a new project, creates all the corresponding entries into the ProjectOptions table.

Adding a new option should require updating all the projects.

Actually you need to add entries to your ProjectOption table, not just update the Project table itself.

You can have another ON INSERT TRIGGER on table Options. Whenever a new Option is inserted, for a certain ProductId, look for all projects having that ProductID, and add the corresponding (ProductId, OptionID, DefaultValueForOptionID /* or NULL */) entries into your ProjectOption table.


Apart from doing this via triggers, you may consider doing the same at the application layer. If the user inputting the new options needs to perform something (like setting specific values for existing projects), this cannot be handled at the database layer.