CODEWITHSUNDEEP
mysqle-commerce
Sakshi Sharma
Sakshi Sharma

Reputation: 1472

database design for a e-commerce website

i m making a e commerce website and i am little confused about the manufacturers table . i have a category table in which suppose i have taken as a example 2 values "mobile phones and ipods" and the sub category table contains the category and subcategory value now the sub category has 2 values such as "gsm" and "cdma" and according to their category value "1" which is from the category table value "mobile phones". now how would i make manufacturers table according to that the same brands would be used by many categories . can any one help me in making a neat manufacturers table . the idea i had was like i would enter the category name in manufacturers table and put the value as 1 for used 0 for not used

manufacturers table:

id  manufacturer_name  
1   Apple              
2   Htc

Category

id  item_category
1   Mobile Phones
2   Ipods

Sub Category

id  item_category  Sub Category
1   1              Gsm
2   1              Cdma

my manufacturers table IDEA

id  manufacturer_name  Mobile Phones    Ipod's
1   Apple                   1             1
2   Htc                     1             0

the idea seems ok but i really dnt know if there is a better solution . well i case of very large number of categories suppose 30 + categories then its not possible to write each category name in manufacturers table and set their value to 1 or 0 . can anyone suggest me some better idea???

Upvotes: 0

Views: 2401

Answers (2)

greg84
greg84

Reputation: 7629

Instead of your idea, create a manufacturer_category_mapping table that joins one to the other like this:

manufacturer_id  category_id
1                1
1                2
2                1

So each row is literally a mapping between a manufacturer and a category.

This table could then have a composite key of both manufacturer_id and category_id, this would prevent you from mapping a manufacturer to the same category twice.

Hope this helps.

To answer the comment, 5 manufacturer_id with category_id of 1 would look like this:

manufacturer_id  category_id
10               1
11               1
12               1
13               1
14               1

Upvotes: 1

Chris Gessler
Chris Gessler

Reputation: 23123

You need to add a product table. Then tie each product to a category / sub-category and tie the manufacturers to the products they make with a many to many relationship.

Manufacturer
- ManufacturerID
- ManufacturerName
- etc..

Product
- ProductID
- ProductName
- etc...

Manufacturer_Product
- ManufacturerID
- ProductID


select m.ManufacturerName, p.ProductName
from dbo.Manufacturer m
join dbo.Manufacturer_Product mp on mp.ManufacturerID = m.ManufacturerID
join dbo.Product p on p.ProductID = mp.ProductID

Upvotes: 0

Related Questions