多对多关系中的问题

Question

I have two tables jewelry(j_id,j_name,Description) and style(style_id,style_name,image) each table has a many to many relationship with the other table, as one jewelry product can have multiple styles and there are more product of same style, so the relationship is many to many.

Now my question is how can I relate these tables i.e I want to insert a single record in jewelry table and there should be multiple styles for one jewelry product.

On jewelry html page I want to put the style in multiple select dropdown list which populated dynamically from style table and if the user want to select two style for the same product, they can.

Answer 1

I would recommend 4 tables, 3 for the jewellery and style and the fourth one for orders.

Jewellery : jid (PKEY),jname,description

Style: sid (PKEY), sname, description

Product : pid (PKEY), sid(FKEY), jid(FKEY) - One Product defines a unique combination of style and kewellery

Order : oid(PKEY), pid(FKEY), other details(name, address etc.)

For each jewellery, recover associated styles using the product table and populate the fields on the html page. For each order, store the product id, which uniquely identifies both the jewellery and style.