0

Ayo guys! So, I'm trying to design a database for a marketplace where customers can buy and vendors can sell. Pretty simple.

A customer can never be a vendor or viceversa. The challenge I'm facing is the following: I'm trying to segregate/separate/group via relational schemas the entities in my database, as follows:

  1. Geographic will refer to country, district and city;
  2. Common will refer to anything that can be shared as a resource between the main entities which are products, customers and vendors;
  3. What I don't know exactly how to tackle is how to separate the customer, vendor and product entities.

I was thinking that:

  1. For customer to have everything what's about them within a schema called [Customer].[TableName] where tableName could be: profile, settings, addresses and so on.
  2. For vendor as for the customer except for the fact that they can add, publish and sell items, share the same information as a customer but under [Vendor].[TableName];

Here's the challenge: about products that only the vendors can add and the customers can only buy. Each product would belong to a category and a subcategory. Also, each product could either be sold as is or have variants (i.e.: size) and each variant will have different options (i.e.: small/medium etc) and therefore, different prices for each combination of variant + option.

Initially, I was thinking to place this unde the [Vendor] schema but to me it looks more that it belongs to the [Product] schema. Also, adding so many tables like [Vendor].[ProductVariant] + [Vendor].[ProductVariantOption] and so on, seemed a bit overwhelming.

So, I wanted to ask? How would you design this database if you were me? Any tips is highly appreciated.

1
  • Nice. But why a customer cannot be a vendor in the first place? Because in the reality this doesn't happen. If you have an account on eBay or Amazon you can do both Jun 20, 2023 at 20:35

1 Answer 1

1

I don't think you should separate things into so many schemas per se, seems to be too granular.

Rather, the things you're calling schemas sound like what would be your tables themselves, e.g. Customers, Vendors, Products. And each row in each of those tables is an instance of that object, essentially.

ProductCategories would be its own table where you could have a column to reference its parent category which would indicate it's a subcategory.

The variant product options you're talking about are typically known as dimensions and are stored in correlating dimension tables such as SizeDimensions, ColorDimensions, etc. One could store the adder price of each type of dimension in each of those tables.

The thing with product options is that those won't be defined in advanced by me but the vendor will be able to define

In this case, then you can have a more generalized ProductOptions table with the columns (OptionId, OptionName, OptionValue, Price). It's a little bit of an EAV anti-pattern, but because these are dimensions, you probably wont be aggregating on the OptionValue column anyway. And if you need to sort them then you can also add a SortValue column too.

2
  • The thing with product options is that those won't be defined in advanced by me but the vendor will be able to define as it's adding a new product. It's not mandatory to have them but they could exist.
    – cdrrrrr
    Jun 21, 2023 at 8:02
  • @cdrrrrr Please see my updated answer to that, which recommends a more generic options table then.
    – J.D.
    Jun 21, 2023 at 11:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.