CODEWITHSUNDEEP
mysqldatabase-design
w3uiguru
w3uiguru

Reputation: 5895

How to design database to store attributes of attributes into database

I am designing database for jewellery website in which a lot of products will store. While designing tables for attributes of products i stuck. My problem is how store product attributes and there sub attributes and so on in database.

Up to now i have created 3 tables for product and attributes -

  1. tbl_attribute

    Structure: attribute_id*, attribute_name

  2. tbl_products

    Structure: product_id*, category_id(FK), product_name, seo, description, metatags, length, width, height, weight, image, status

  3. tbl_products_attribute

    Structure: product_id(fk), attribute_id(fk), value

I have a situation suppose Necklace have 5 stones (Stone is attribute) and each stones have following sub attributes 1. Stone Name 2. Stone Color 3. Stone Treated Method 4. Stone Clearity 5. Stone Shape 6. Stone Price

and so on etc.... and i have So many attributes like stone so can you please help me how to design the table for these attributes.

Depends on that attributes i have to search(FILTER || Faceted Search) the products in front end.

Like: www.firemountaingems.com

Detailed attributes and sub attributes list:

Alphabatical
Availability (Sold Individualy, Sold in Bulk)
Birth Stone
Brands
Color (red, green, blue)
Design Type
Gender (Male, Female)
Images 
Karat (18k, 22k, 24k)
Link
Make (Hand, Machine, Oxidised)
Making Percent
Material Type (Leather, Gemstone, etc)
Metal Art
Metal Stamp
Metal Type (Gold, Silver, PLatinum etc)
Model
Name
Price
Purity
Shapes (round, oval, emerald, diamond etc)
Short Description
Sides (single, both,)
Size (small, big etc)
Special Price
Status
Stone (Name, Color, Treated Method, Clearity, Shape, Price, Main Stone Color (Red, Pink, Green))
Stringing Material
Warranty
Wastage Percent
Weight
Wire - Wrapping Wire

Up to now i have search so many tutorials and article on net as well as on stack overflow.

Your help is highly appreciated.

Upvotes: 1

Views: 3602

Answers (1)

Elvin
Elvin

Reputation: 2291

In this case you cant afford creating tables like this the best approach is to have only 1 product table and 1 attributes table which will be having all attributes of a product but with columns attribute_level and parent_id where parent_id refers id of same table.

Example: products table:

ID | Name
----------
1  | Necklace


ID | ParentID | AttributeName | ProductID | AttributeLevel | AttributeDescription
----------------------------------------------------------------------------------
1  |          | Stone         | 1         | 1              | stone description in general
2  | 1        | StoneName     | 1         | 2              | Ruby

In this way you can make hirerchy of attribute levels within one table.

Upvotes: 1

Related Questions