I'm developing a new eCommerce platform as a service, and I have some struggles with product variations database. So I want basically to build Shopify like platform, and I'm still on database design, and here is what I did.
Schema::create('products', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->longText('description');
$table->set('type', ['Single Product', 'Variation']);
$table->unsignedBigInteger('price_id')->nullable();
$table->unsignedBigInteger('category_id');
$table->timestamps();
});
Schema::create('product_prices', function (Blueprint $table) {
$table->id();
$table->float('real_price', 8, 2);
$table->float('sale_price', 8, 2)->nullable();
$table->float('delivery_price', 8, 2);
$table->timestamps();
});
Schema::create('variations_attributes', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->timestamps();
});
Schema::create('variations_attributes_names', function (Blueprint $table) {
$table->id();
$table->unsignedBigInteger('attribute_id');
$table->string('name');
$table->timestamps();
});
Schema::create('product_variations', function (Blueprint $table) {
$table->id();
$table->unsignedBigInteger('product_id');
$table->unsignedBigInteger('price_id');
$table->string('image');
$table->string('variation_name');
$table->string('sku_id');
$table->string('variation_ids'); // 123/Color, 321/Size, etc...
$table->timestamps();
});
Schema::create('products_sku', function (Blueprint $table) {
$table->id();
$table->unsignedBigInteger('product_id');
$table->unsignedBigInteger('product_variation')->nullable();
$table->integer('sku')->unique();
$table->integer('qty');
$table->timestamps();
});
(This is still unfinished database, and there will be more added later, as well foreign keys are not correctly formed, but you still get the idea)
So now, what is my idea to structure variations. Client will have options in front end and select as many as he likes from variations_attributes (Size, Color, Ships from etc...) depends what he add there, and in variations_attributes_names would be (Red, Green, M, L, XXL, China, Russia, etc...). Just like in Shopify form, when he select attribute, and add attribute names it will create him a form like (for example he selected all 3 attributes, with Color Red and Green, Size M and L, and Ships from Russia and China) form will look like:
RED/M/China - price (custom price) and sku
RED/M/Russia - price (custom price) and sku
RED/L/China - price (custom price) and sku
RED/L/Russia - price (custom price) and sku
GREEN/M/China - price (custom price) and sku
GREEN/M/Russia - price (custom price) and sku
GREEN/L/China - price (custom price) and sku
GREEN/L/Russia - price (custom price) and sku
So basically it will multiple all options that he selected and give him option to add custom prices for all variations.
So in that case my idea for product_variations database for field variation_ids is to get ids of selected attributes and store them like
1(attribute id)\1(attribute name id);1(second attribute id)\1(second attribute name id);1(third attribute id)\1(third attribute name id)
So in this case, when he select on front end attribute value it will select him the value he wanted. But in that case if he select first size and not color, it will give an error, and that would be a problem.
Any ideas how to structure that database?
