CODEWITHSUNDEEP
mysqldatabasedatabase-designforeign-key-relationship
Chinmoy
Chinmoy

Reputation: 1754

What is the best way to design Seller, Category and Product tables?

I want to store a list of Sellers, who sell a certain Category of products each, and then, each Seller will have Products from these Categories.

What is the best way to design this? Currently, what I have is three tables, Seller, Category and Product designed in this way.

How do I link Seller and Category? I feel like I am creating too many relationships here.

One way would be to create a separate table with indices for SellerId and CategoryId. Also, I think I will need a separate table for SellerId and ProductId too. Is there a better way to approach this design?

1 seller can have multiple categories,

1 category can have multiple sellers,

1 category can have multiple products,

1 product can again, have multiple sellers,

1 product can have only 1 category,

and last, 1 seller can have multiple products.

enter image description here

Upvotes: 0

Views: 1985

Answers (1)

David
David

Reputation: 218808

Based on your update, consider this structure...

Seller
----------
ID
etc.

Product
----------
ID
CategoryID
etc.

SellerProduct
----------
SellerID
ProductID

Category
----------
ID
etc.

In this case:

  1. Seller and Product are aggregate roots
  2. A Seller can be linked to multiple Products
  3. A Product can be linked to multiple Sellers
  4. Category is an attribute of a Product
  5. Sellers are "linked" to Categories implicitly by the Products they sell

Would a Seller ever need to be linked to a Category for which that Seller has no Products? If not, this structure should do the trick. If a Seller needs to be linked to another Category, that Seller simply needs to link to a Product of that Category.

By demoting Category to a value type instead of its own entity, you don't need to link other entities to it.

Upvotes: 1

Related Questions