09-28-15, 09:52 #1Registered User
- Join Date
- Sep 2015
Unanswered: Handling stocks for product variants
In a custom ecommerce web site, I need to handle products, product options and product variants. I need all of them to be dynamic. So admin should be able to create products, product options and assign product options to product variants. I have designed following db schema for this requirement. I want to focus on product management flexibility as well as performance here.
products: id, name, description, qty, base_price, parent_id...
options id, name
option_values id, name, option_id
product_options product_id, option_id
product_option_values product_id, option_id, option_value_id, qty, price
The problem is that I cannot store quantity or price for a product like Red XL T shirt. I mean a product with multiple options. Can anyone help me solve this?
Thanks in advance.
09-28-15, 10:00 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
The problem lies in how optional are your options. In other words do all shirts have the same color options, or do different shirts have different choices (or no choice) of color? Do all shirts come in all sizes, or are some only offered in one size (or one size fits all)?
Since most inventories require a continuous variation of colors, sizes, etc which vary by product let's consider that case first. The way that I'd handle this problem is to use a many-to-many linking table. This allows one shirt to have no options at all, but other shirts to have a size and a color. Depending on the choices that you make it can be challenging to only allow one color or size per item and that limitation may or may not be appropriate.
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.