Database Design for a cosmetic e-commerce brand

Question

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.
Thanks

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.