I have three tables:
m:n relation between
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-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
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 T40 might have tags:
fresh tree and
fuzzy rolldie might have tags:
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
winter tyres, chevy with
silly things, etc.
The purpose of the tags is just to say which accessories can go with which products.