I am developing an e-commerce store which has cosmetic products. Now the challenge I am facing is that seller has a set of nail polish/ lipsticks boxes etc and those items inside can be sold individually as a single product.
There is no issue creating a single product table and put everything there but I don't want seller to keep adding same type of product over and over again which just differs in color shade.
I tried to group them by creating a product table having product name, category, brand and putting individual products inside as a product_variants table which has price, sku, attributes and everything but that made a lot of confusion even for me as I am calling for variants and showing them as individual product on main site, even on admin panel when looking at product list, actual products are inside of product list shown.
My question is How can I make this simpler for me and for seller also. Is there any other way for simplifying this kind of relationship or should I just create a parent table/group and link back all products to them for easy read.

  • I think an important thing to consider is the language and how the business treats the products when selling them. Is there one lipstick that comes in multiple options (multiple colors)? Would a user look up the lipstick, then select a color on the product page? Or would they all be presented as separate colors? Then, consider how closely the internal admin page resembles the customer-facing website.
    – AMtwo
    Nov 21, 2021 at 22:36
  • Context:: I've worked at a few different retail companies, and the data model is always either (1) closely modeled based on the way products are presented to the user, and admin tools also basked on how it's presented to the user, or (2) a "boat anchor" of pain where the data model is used for historical reasons, despite being painful to use now. The "best" data model will be based on knowledge of the user experience. Adding UX detail to your question will be very helpful.
    – AMtwo
    Nov 21, 2021 at 22:41

1 Answer 1


Some people do actually consider products of a different color as two separate products and store them with two different unique identifiers (such as SKUs) in their Products table. But if your business rules considers two products of different colors as the same product by SKU then you can have a separate Dimensions table with attributes about the Product such as color, size etc.

Based on the example schema you provided, it seems like you're trying to squeeze all the attributes into the ProductName which is a denormalized way to store that data. Rather you should split the attributes out to separate columns (even if you store those columns in your Products table itself). For example, you can have a ProductName column that repeats the name Some Fancy Nail Polish and have a column for Color with values like Red, Green etc and even another column called Shade with values for Shade-01, Shade-02 etc, and of course the SKU column to uniquely distinguish between each Product.

Structuring the data into relevant columns like this helps normalize how you store your data and makes it easier to maintain and even utilize throughout your application. When you display it in the application, you have more control and granularity to show the data across separate fields, and to do things such as have the user select from the distinct list of ProductNames (smaller list than the number of records in the Products table) and then showing only a smaller subset of the Products that share that ProductName.

This is a basic implementation to explain the concept, but if your database has to catalog other products besides nail polish, then you'll want to consider normalizing your table further, and perhaps have a table specific to nail polish products vs a table for wig products, etc, each with their own relevant columns. And you could still have a single Products parent table that essentially stores the SKU and any generic columns applicable to all product types.

  • SKU will be different for each one. Only hurdle is how to make it easy to add, read, view in admin panel. Nov 21, 2021 at 15:12
  • Normally an e-store would have the application dealing with that, not just an admin panel. Nov 21, 2021 at 15:13
  • @MysticalSam Not sure I follow why it wouldn't be easy? If you do end up storing each unique SKU in your Products table, you could at least simplify it in the Admin Portal by making them select a "product" from a distinct list of the product names as the first step in funneling the data down, then showing them just the SKUs of that product name.
    – J.D.
    Nov 21, 2021 at 17:33
  • Let's put it this way. I want to group the similar kind of items in admin panel so that they can be added with ease in db and view with ease. Nov 21, 2021 at 18:02
  • Let's assume a I have a box of cosmetic products and I want them to add to website. tbl_products ---------------- id | product_name | category 1 | Some Fancy Nail Polish | Nail Polish 2 | Branded Lipstick | Lipsticks tbl_product_variants --------------------- id | product_id | product_variant_name | product_variant_sku | product_variant_price 1 | 1 | Matt Red Shade-01 | SKU-1 | 10 2 | 1 | Matt Green Shade-03 | SKU-2 | 8 . . 12 | 1 | Glossy Yellow Shade-03 | SKU-1 | 10 Nov 21, 2021 at 18:02

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.