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

Question

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

Are the relationships many-to-many? If any given Seller can have multiple Category records and any given Category can have multiple Seller records then they would need such a linking table between them. What's unclear to me, then, is the Product relationships. If a Product is owned by both a Category and a Seller then there's no guarantee of the integrity of that relationship. That Seller might not be linked to that Category. Should a Product be owned only by a Category perhaps? And any Seller with that Category has access to that Product? — David
Yes. these relationships are many-to-many. So, I can go ahead and create a Junction table between Category and Seller, but I also need to store Products from specific sellers. So, in essence, 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 [Updated the question with this use-case] — r3st0r3
My concern is that these relationships can end up "lying" about each other. Seller 1 can be linked to Category A, but can have Product X which is in Category B. There's nothing in the structure to stop this from happening. Should Category really even be an entity in this case? Or just a lookup? Would the business logic be satisfied if, for example, Products are owned by Sellers (a FK to Seller on Product) and Products have Categories (a FK to Category on Product). This would create an implied link between Seller and Category, just as an aggregate of the Categories of the Seller's Products. — David
That makes a lot of sense. However, I wanted to keep the Product entity free of duplicates. In this case, there might be Sellers A, B and C all of who are selling Product 1 from Category 'Books'. I will end up storing 3 product records in this case. Is there a way to avoid that? — r3st0r3

David David · Accepted Answer · 2015-01-16T16:23:01

Based on your update, consider this structure...

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

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

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

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

In this case:

Seller and Product are aggregate roots
A Seller can be linked to multiple Products
A Product can be linked to multiple Sellers
Category is an attribute of a Product
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.

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

1 Answers