Relational database design advice for laravel 4 based e-commerce application

Question

I develop an e-commerce application with Laravel 4 and need an advice to solve my database design problem.

The Problem:

My client has products which have multiple variations/options. For example:

Product1 = Apple Iphone 5

Color Option : Black

Option1 For Black: 16GB

Stock for Option1 : 15

Sales Price for Option1 : $900
Option2 For Black: 32GB

Stock for Option2 : 32

Sales Price for Option2 : $1.200
Option3 For Black: 128GB

Stock for Option3 : 24

Sales Price for Option3 : $1.700

Color Option : White

Option1 For White: 32GB

Stock for Option1 : 9

Sales Price for Option1 : $930
Option2 For White: 64GB

Stock for Option2 : 12

Sales Price for Option2 : $1.270

Product2 = Motorola Xoom 9868AB Case

Material Option : Leather

Option1 For Leather: Grey

Stock for Option1 : 90

Sales Price for Option1 : $12
Option2 For Leather: Pink

Stock for Option2 : 12

Sales Price for Option2 : $12.70

Material Option : Hard Shell

Option1 For Hard Shell: Black

Stock for Option1 : 51

Sales Price for Option1 : $32.90

My approach:

As you see above, there is many products with different type of options,stocks,prices. I've tried something like the following with belongsToMany():

Table: products

id 
name
created_at
updated_at

Table: variations

id
title
parent
created_at
updated_at

Table: product_variation

id
products_id
variations_id
stock 
purchase_price 
sales_price
created_at
updated_at

The Products model:

<?php
class Products extends Eloquent {
   protected $table = 'products';
   protected $fillable = array('name');
   public function variations() {
   return $this->belongsToMany('Variations','product_variation')
               ->withPivot('purchase_price', 'stock', 'sales_price')
               ->withTimestamps();
    }
}

How should be the Orders Model? The orders would hasMany() products and Variations. How would be your approach to the problem?

why dont you just have each product variation as its own product? i.e. Product 1 is Iphone5 White, Product 2 is Iphone Black etc — Laurence, Aug 18 '13 at 13:25
It is really very old fashioned way. Because it is really very hard to manage the store. Both front-end and back-end. Let's imagine that as a T-shirts store and you are a customer who want to buy Adidas 345 thirt whit black color and XL size. The navigate, finding the product is like a nightmare... — dr.linux, Aug 18 '13 at 13:39
See [MySQL Shopping Cart Structure](http://stackoverflow.com/a/10398545)? — eggyal, Aug 18 '13 at 13:56
Thaks it is very useful for the question. Still looking for the eloquent relation on your suggestion. — dr.linux, Aug 18 '13 at 15:43
BTW in your structure the variations has no own stock and prices. — dr.linux, Aug 18 '13 at 15:50

score 1 · Answer 1 · answered Aug 24 '13 at 07:27

The option table should be hasAndBelongsToMany() with the Products table. Then each stock, and price should be hasAndBelongsToMany() to the options table.

Products table:

id
product_name
created_at
updated_at

Options table:

id
option_name
created_at
updated_at

option_product table:

id (optional(
option_id
product_id
created_at
updated_at

Stock table:

id
count
created_at
updated_at

option_stock table:

id (optional)
option_id
stock_id
created_at
updated_at

Prices table:

id
price
created_at
updated_at

option_price table:

id (optional)
option_id
price_id
created_at
updated_at

This will give you the ability to have one entry of Option color purple that could be applied to many different products, and many different prices for purple. Also your queries wont be too terrible as you will be able to eager load your options and other tables (see http://laravel.com/docs/eloquent#eager-loading)

<?php
$products = Product::with(array(
   'options',
   'options.stock',
   'options.prices'
))
->get();

The admin side of creating products for the site could be done pretty easy, but that is a different question.

Hmm good advice, many thanks. But i didn't see in documentation hasAndBelongsToMany(). Did you mean belongsToMany()? — dr.linux, Aug 25 '13 at 12:55
Yes, belongsToMany() is the one you want. http://laravel.com/docs/eloquent#many-to-many The hasAndBelongsToMany() is the old Laravel 3 trying to slip in. — codivist, Aug 26 '13 at 14:48
Good luck! Python is pretty powerful, you'll probably have no problem getting what you need out of it. — codivist, Sep 11 '13 at 14:31

score 0 · Answer 2 · answered Aug 21 '13 at 17:59

Each attribute with multiple values should have its own table, where the foreign key is the base item -- for example, you could have iPhone in the base table, with color, memory size, etc. as their own tables. The pricing table then contains the price and all of the foreign keys that completely describe the item (a white, 64GB, iPhone).

You might want to look up "database normalization" while you are at it.

score 0 · Answer 3 · answered Aug 27 '13 at 11:33

0

I suggest you to take a look at noSQL databases like http://www.couchbase.com/. In my case I had similar issues with some products / services in some projects. Json / object storing helped me, and I'm really glad I decided to move to couchbase.

answered Aug 27 '13 at 11:33

0x_Anakin

3,229
5
47
86

score 0 · Answer 4 · answered Aug 27 '13 at 17:01

My guess is that some products have variations, and some don't. At some point you will want to create orders for products (and variations). If products and variations are modeled as completely different entities, modelling orders and other things will become cumbersome. I'd propose a simpler solution where products and variations are both modelled as products, and where the variations or options are linked to a parent product via an association table as follows:

Product

product_id (pk)
name
price

Product_Option_Map

parent_product_id (fk to product.product_id)
option_product_id (fk to product.product_id)
option_number (if necessary to ensure ordering)

If necessary, you could add a 'type' column to the Product table which will allow your systems to easily identify a product which has options. Of course this can always be inferred from the presence of Product_Option_Map rows where the parent_product_id matches the given product_id.

I noticed that one of the other respondents modeled prices in their own table. I'd encourage you to discuss pricing requirements with your customer, as real world systems can rarely get away with simply storing a single price per product. You will likely find that the there are all sorts of promotions, volume discounts, coupons, pre-arranged customer discounting schemes, etc... Storing a 'sell-price' at the product table is very likely going to be inadequate.

score 0 · Answer 5 · answered Jul 05 '18 at 04:59

Take a look at my migration file i have separated with attributes and properties of products.

Attribute is Front Facing or Customer can changed the Attribute (e.g: color , size)
Properties are fixed attribute of a product like: BedSheet Mateial (e.g: cotton)

https://github.com/avored/framework/blob/master/database/migrations/2017_03_29_000000_avored_framework_schema.php

Relational database design advice for laravel 4 based e-commerce application

5 Answers5