Level 63
What is your question ?
1 like
Sep 11, 2022
4
Level 4
Product Variation Database
Hi. I am working of a product website, each product has different variations. Here's a similar website I am trying to clone. https://printivo.com/product/a4-notepad on the page I shared above, the product can be customize by spcification which are paper, lamination, Binding and Cover and each customization affect the product price. Here is the solution I can think of. I can't tell if this is a good solution, but the problem with my design is the difficulty of entering product variations, if a product has 5 customizable specifications I will have to enter about 25 different variations.
Category
id name
1 Business Cards
2 Notepads and Jotters
==================================
<!-- we use the price here to indicate the starting price for the product -->
Product
id name Description price
1 A6 Notepad Let your cu... 10,925
2 A4 Notepad Delight your customers.. 27,600
3 Two-sided Business Cards Some description... 8,855
4 Mini Business Card Some description... 3,795
Category_Product
category_id product_id
1 3
1 4
2 1
2 2
Options
id name
1 Paper Thickness
2 Corners
3 Lamination
4 Paper
5 Binding
6 Cover
OptionValue
id Option_id value
1 1 300gsm Stock
2 1 600gsm Super Thick 6000
3 2 Square
4 2 Rounded
5 3 Matte
6 3 Gloss
7 4 Plain Innert Sheets
8 4 Ruled Innert Sheets
9 5 Spiral
10 5 Perfect
11 6 Soft
12 6 Hard
<!-- Because I have 4 different options for this product, there has to be 16 combinations variations -->
Product Variation
id product_id SKU price Desc
1 2 PMSPSJot 16,330 Plain Inner Sheets | Matte Lamination | Spiral | Soft
2 2 PMSPSJot 22,330 Plain Inner Sheets | Matte Lamination | Spiral | Hard
3 2 PMSPSJot 28,330 Plain Inner Sheets | Matte Lamination | Perfect | Soft
4 2 PMSPSJot 34,330 Plain Inner Sheets | Matte Lamination | Perfect | Hard
5 2 PMSPSJot 16,330 Plain Inner Sheets | Gloss Lamination | Spiral | Soft
6 2 PMSPSJot 22,330 Plain Inner Sheets | Gloss Lamination | Spiral | Hard
7 2 PMSPSJot 28,330 Plain Inner Sheets | Gloss Lamination | Perfect | Soft
8 2 PMSPSJot 34,330 Plain Inner Sheets | Gloss Lamination | Perfect | Hard
9 2 PMSPSJot 16,330 Ruled Inner Sheets | Matte Lamination | Spiral | Soft
10 2 PMSPSJot 22,330 Ruled Inner Sheets | Matte Lamination | Spiral | Hard
11 2 PMSPSJot 28,330 Ruled Inner Sheets | Matte Lamination | Perfect | Soft
12 2 PMSPSJot 34,330 Ruled Inner Sheets | Matte Lamination | Perfect | Hard
13 2 PMSPSJot 16,330 Ruled Inner Sheets | Gloss Lamination | Spiral | Soft
14 2 PMSPSJot 22,330 Ruled Inner Sheets | Gloss Lamination | Spiral | Hard
15 2 PMSPSJot 28,330 Ruled Inner Sheets | Gloss Lamination | Perfect | Soft
16 2 PMSPSJot 34,330 Ruled Inner Sheets | Gloss Lamination | Perfect | Hard
<!-- Because I have 4 different options for this product, there has to be 4 records for each product variation-->
Variation_values
product_id variant_id option_id value_id
2 1 4 7
2 1 3 5
2 1 5 9
2 1 6 11
2 2 4 7
2 2 3 5
2 2 5 9
2 2 6 12
2 3 4 7
2 3 3 5
2 3 5 10
2 3 6 11
Level 4
Look at the reference website. Is this the best database design for the project?
Just to over the user access to customize the product, I have to create many variations. Is there a better way to do this. Thanks
1 like
Level 73
@successdav I suggest reading this post, and focus on the one-to-many with different properties. I think that is what you might need.
2 likes
Level 80
@successdav When you have products with variants, it’s usually because a single products has many different SKUs (stock keeping units). Think of say, a t-shirt. A t-shirt might come in many different sizes, or even colour, but it would be classed as the same “product”. It would just be there would be a SKU representing each size and colour combination that t-shirt came in.
So, it would be the same with your notebooks. There’s a single notebook “product”, but there would be a SKU representing each configuration that product is available in.
So that being said, you would have a Product model, which has-many SKU models. A Product could also have many Attribute models. These attributes would be your variations like Paper, Lamination, Binding, and Cover. Then you could have a attribute_sku pivot table that specifies the values for each attribute and SKU combination. For example, SKU1 might have a value of Plain inner sheet for the Paper attribute, Matte for the Lamination attribute, and so on.
The migrations for these would look something like:
Schema::create('products', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->text('description')->nullable();
// Any other common product columns like category, brand, etc.
$table->timestamps();
});
Schema::create('attributes', function (Blueprint $table) {
$table->id();
$table->foreignId('product_id')->constrained()->cascadeOnDelete();
$table->string('name');
$table->timestamps();
});
Schema::create('skus', function (Blueprint $table) {
$table->id();
$table->foreignId('product_id')->constrained()->cascadeOnDelete();
$table->string('sku'); // The SKU value itself, i.e. SKU1
$table->char('currency_code'); // i.e. USD
$table->unsignedInteger('unit_price');
// Any other SKU columns, like EAN, GTIN, etc.
$table->timestamps();
});
Schema::create('attribute_sku', function (Blueprint $table) {
$table->primary(['attribute_id', 'sku_id']);
$table->foreignId('attribute_id')->constrained()->cascadeOnDelete();
$table->foreignId('sku_id')->constrained()->cascadeOnDelete();
$table->string('value'); // The value for this attribute and SKU combination
});
The pricing information is placed on the SKU model as different attributes may carry different pricing. For example, a better paper stock may cost more than regular paper stock, or binding may carry a higher fee.
3 likes
Please or to participate in this conversation.