I'm building a custom eCommerce solution for a client. I came up with this database design:
+---------------+ +---------------+
| PRODUCTS |-----< SKUS |
+---------------+ +---------------+
| id | | id |
| name | | product_id |
+---------------+ | sku |
| | price |
| | qty |
| +---------------+
| |
+-------^-------+ +------^------+
| VARIANTS |------< SKU_VALUES |
+---------------+ +-------------+
| id | | id |
| product_id | | product_id |
| name | | variant_id |
+---------------+ |variant_option_id |
| | sku_id |
| +------v------+
+-------^-------+ |
|VARIANT_OPTIONS|-------------+
+---------------+
| id |
| product_id |
| variant_id |
| name |
+---------------+
variants and its options are dynamically added by the admin. So for example lets say we have a T-shirt it can have many colors and sizes. Example
T-shirt :color=red,blue :size=small,large
This is would be represented in the database like so:
products:
------------------
| id | name |
------------------
| 1 | t-shirt |
------------------
variants:
------------------------------
| id | product_id | name |
------------------------------
| 1 | 1 | size |
------------------------------
| 2 | 1 | color |
----------------------------
variant_options:
---------------------------------------------
| id | product_id | variant_id | name |
---------------------------------------------
| 1 | 1 | 1 | small |
---------------------------------------------
| 2 | 1 | 1 | large |
----------------------------------------------
| 3 | 1 | 2 | red |
---------------------------------------------
| 4 | 1 | 2 | blue |
----------------------------------------------
skus:
-----------------------------------------------
| id | product_id | sku | price | qty |
------------------------------------------------
| 1 | 1 | 4xx01 | 140 | 5 |
------------------------------------------------
| 2 | 1 | 4xx02 | 140 | 10 |
------------------------------------------------
| 3 | 1 | 4xx03 | 160 | 8 |
------------------------------------------------
| 4 | 1 | 4xx04 | 160 | 15 |
sku_values:
----------------------------------------------------------------------
| id | product_id | variant_id | variant_option_id | skud_id |
-----------------------------------------------------------------------
| 1 | 1 | 1 | 1 | 1 |
-----------------------------------------------------------------------
| 2 | 1 | 2 | 3 | 1 |
-----------------------------------------------------------------------
| 3 | 1 | 1 | 1 | 2 |
-----------------------------------------------------------------------
| 4 | 1 | 2 | 4 | 2 |
-----------------------------------------------------------------------
| 5 | 1 | 1 | 2 | 3 |
-----------------------------------------------------------------------
| 6 | 1 | 2 | 3 | 3 |
-----------------------------------------------------------------------
| 7 | 1 | 1 | 2 | 4 |
-----------------------------------------------------------------------
| 8 | 1 | 2 | 4 | 3 |
-----------------------------------------------------------------------
What do you guys think of this implementation? is there a better way?
I'm actually about to begin wrestling with this very problem, as I'm beginning development of an open source laravel e-commerce package eco-system.
Firstly i recommend looking into how Sylius have approached this problem. All products are a varient, even products that donet have variations.
Or you could go the magento route, there are no such things as varients. All varients ARE products.
@andrewmclagan check this out this helped me out a lot http://www.colab-aktiv.com/?page_id=25
I'm going with my personal project with a more Sylius approach. I've also dug deep into the openCart database model which is the link I shared you.
I would love to help out with your eCommerce Laravel project do you have a github for it?
@rodzzlessa Yeah, I also decided to go with the Sylius "Master Variant" architecture long ago. It just makes more sense. Think of it in terms of an SKU, to spell it out Single - Stock - Unit.
refers to a stock keeping unit, a unique identifier for each distinct product
Each variation of a product is really a product in itself. THAT is why I love the way Sylius addresses this issue. Even a Product with no options / variation still has one master variation, purely to make developing more consistent.
* T-Shirt
* Size
* * Small
* * Medium
* * Large
* Colour
* * Red
* * Green
* * Blue
* SKU: XXXX - Size: Small - Colour: Red
* SKU: XXXX - Size: Small - Colour: Green
* SKU: XXXX - Size: Small - Colour: Blue
* SKU: XXXX - Size: Medium - Colour: Red
* SKU: XXXX - Size: Medium - Colour: Green
* SKU: XXXX - Size: Medium - Colour: Blue
* SKU: XXXX - Size: Large - Colour: Red
* SKU: XXXX - Size: Green - Colour: Red
* SKU: XXXX - Size: Blue - Colour: Red
Therefore the Models, Entities, Repositories or whatever-you-want-to-call-them would be:
The project is up, its called Jiro. Although work has halted till L5 stabilises, https://github.com/Jiro-Commerce . Feel free to make some pull requests.
@andrewmclagan The link you shared takes me to a 404 error but I was able to find the Product package
Hi @rodzzlessa, remove the last dot within the URL:
https://github.com/Jiro-Commerce
The link took that dot.
@andrewmclagan I cant see any database migration regarding
Product Option OptionValue Variation
In your https://github.com/Jiro-Commerce project Could you point me? thanks
@rodzzlessa An unrelated curious question, what tool did you use to generate the text-based (ASCII) representation of the table diagrams?
@Muajjam-imu This question was 7 years old…
It helped me
Please or to participate in this conversation.