I have three tables: Product, Accessory and m:n relation between Product and Accessory. There are currently around 200 products and around 600 accessories. The user wants to have tags on the Accessories and then add the tags to the Products to create the relationship among them. Both Products and Accessories can have multiple tags.

The tags are only used to improve adding/creating the relationships among them, so the feature is for administrators only. The most used queries then are select "something" from Product inner join Accesory.

My first thoughts are that there should be a Tag table and two relation tables Tag-Product and Tag-Accessory. Once a tag is added to a Product or an Accessory to a Tag that is linked to a Product, the system modifies the relation table ProductAccessory.

What would be my other options?

What would be an optimal solution?


+---------+   +--------------+
| Product |   |  Accessory   |
+---------+   +--------------+
| toyota  |   | tyres T20    |
| hyundai |   | tyres T40    |
| chevy   |   | tyres T40s   |
+---------+   | fresh tree   |
              | fuzzy rolldie|

tyres T20 might have tags: tyres winter tyres

tyres T40 might have tags: tyres summer tyres

fresh tree and fuzzy rolldie might have tags: silly things

Not all accessories are available in all products, but in 80 percent of cases some group/tag of accessories is available. There are also cases when 1 accessory is available only in one product.

So you could have toyota tagged with tyres, hyundai with winter tyres, chevy with tyres and silly things, etc.

The purpose of the tags is just to say which accessories can go with which products.

Your Answer

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

Browse other questions tagged or ask your own question.