0

I need to setup a database for my product. I need to store product name, product prize, product detail, and product features in this database. One product has more than one feature now how can I setup this database?

I have setup a database with two tables

  1. Product_detail has id, p_name, p_detail, p_prize and
  2. Product_ features has id,p_ features (there are one more feathers for one database)

Is it correct? If correct so how can I make relation between a product and its features? And how can I retrieve the data from database?

If this is wrong so how can I make this?

Md.abdullah Al hady
  • 15
  • 3

1 Answers1

1

This is a common relational design pattern used to resolve many to many relationships like the one you describe. You need a link table to link Product_detail to Product_features.

I would rename your tables so you had Product, Feature, and the link table ProductFeature.

ProductFeature would consist of records containing product id and featureid (and probably more than one record per product.

So to retrieve all features for product with id 1

SELECT *
FROM product p
INNER JOIN productfeature pf ON pf.productid = p.id
INNER JOIN feature f ON pf.featureid = f.id
WHERE p.id = 1

You will need to add appropriate foreign keys, and you might consider encapsulating the query in a view (SQL Server construct, so not sure what the MySQL equivalent is)

Benny Hill
  • 6,191
  • 4
  • 39
  • 59
Matt Evans
  • 7,113
  • 7
  • 32
  • 64
  • Adding to embo's answer, keep in mind that similarly named product features don't always represent the same thing, so it's frequently useful to make thte latter product dependents - eg shirt size is not the same as pant size, and xs/s/m/l/xl is not necessarily 1/2/3/4/5. – Denis de Bernardy Jun 22 '11 at 16:59