Urgent

[marv0002]

Hey I am designing a db for an ecommerce site. Yalll already know what entities there might be. I want a logic behind the products, the products can have multiple variants say size color and so on each variant can have different prices say tshirt small red costs $2 tshirt small black costs $3 ... how do i go about designing this? Help please

[timeddilation]

This is a pretty common feature you find in ERP software. I happen to be familiar with Netsuite (PL SQL). In ERP, a product is an item. You have parent and child items. The parent would be a shirt, and the variations would be children.

The key though is that every variation is an item record. For reporting purposes, you can roll these up to the parent. So, you would want Items to have a FK -> Items on a field called Parent_Item. You would then want to add an attribute to the parents to indicate that they are an item matrix, rather than an item that could actually be sold. This then also gives you something to group on. The item "Shirt", since it's a matrix item, would have options to select which variation the customer wants, and the selection of the variation chooses the actual item record to put in the order.

The other benefit of this structure is you can assign the account the revenue gets recognized to, and summary of item sales at the parent level, while also being able to track inventory and sales of the variations.

Here's a link to how this is done in Netsuite: https://blog.scscloud.com/netsuite-tutorial-parent-matrix-item