Search code examples
phplaraveldatabasedatabase-designlaravel-10

Large store database design in Laravel

I have a Laravel store project that I am confused to develop my products database. My store products are clothes, shoes, pants, laptops, phones, headphones, etc.

Hello friends, good time This database is for the products of my store:

database photo

But as long as I have products like shoes and clothes it works correctly and optimally because they have many options, for example these are the options for product_id=3 which is a dress in the product_options table:

option::1 option::2 option::3
color_id:8 color_id:8 color_id:9
size_id: 3 size_id:5 size_id:5
price: 25 price: 20 price: 20
stock: 5 stock: 9 stock: 8

But for products such as phones, laptops, etc., which have only one option and do not have color_id and size_id at all. I don't know what to do. please help. For example, these are the options for product_id=5, which is a laptop in the product_options table:

option::1
color_id:null
size_id: null
price: 50
stock: 5
Solution

  • In order to only add options which the product requires, as well as having the possibility to add any type of options, you should redesign your database and use a "many to many" relationship.

    1. Remove "color_id" and "size_id" from "products_options"
    2. Rename "products_options" to "variations"
    3. Remove "colors" and "sizes" tables
    4. Create "options" table with fields : id, type
    5. Create a pivot table named "options_variations" with fields: variation_id, option_id, value

    Create all your options in the "options" table:

    $option1 = new Option(['type'=>'color'])
$option2 = new Option(['type'=>'size'])
$option3 = new Option(['type'=>'screen_size'])
$option4 = new Option(['type'=>'battery_life'])

    Create products:

    $product1 = new Product(['title'=>'Dress 1'])
$product2 = new Product(['title'=>'Laptop 1'])

    Create variations:

    $variation1 = new Variation(['product_id'=>1, 'price'=>55])
$variation2 = new Variation(['product_id'=>2, 'price'=>1000])

    And assign your options to the variation:

    $variation1->attach($option1, ['value'=>'red'])
$variation1->attach($option2, ['value'=>'10'])
$variation2->attach($option4, ['value'=>'8h'])

    You can read more about Many to Many Relationships and on how to insert/update related models on laravel.com. If you have a laracast subscription, you can watch https://laracasts.com/series/mysql-database-design/episodes/11.