Laravel E-Shop: Products, Options, Attributes - Database Structure Example

When working with shops and e-commerce projects, one common question is how to structure the DB of products with all attributes, variants, and options, like "iPhone - Black - 2 GB RAM - 1 TB Storage". In this tutorial, we will show our version of doing that.

Here's the visual product page we will get by the end of this tutorial:

Database Structure

First, let's look at all the DB Schema, and we will dive into its details individually.

Initially, it might confuse you about what's going on, but it's pretty simple:

• products In this table, we'll store global product information. For example, product name and description.
• SKUs Here, we will store all the information about a specific product variation. For example, price, stock, images, etc.
• attributes This table will store all the available attributes to be selected. For example, color, size, etc.
• attribute_options This table will store each attribute's available values. For example, red, blue, green, etc.
• attribute_option_sku Is our last table that will tie everything together. It will connect a specific attribute value and a specific SKU.

Our example below will use a simplified version with minimal fields for each table. You can always add more fields to each table to fit your needs.

Building Database and Models

Let's start by creating our attributes table:

Schema::create('attributes', static function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->timestamps();
});

With its Model:

app/Models/Attribute.php

use Illuminate\Database\Eloquent\Relations\HasMany;
// ...
 
class Attribute extends Model
{
    protected $fillable = [
        'name',
    ];
 
    public function attributeOptions(): HasMany
    {
        return $this->hasMany(AttributeOption::class);
    }
}

Next, we'll create our attribute_options table:

Schema::create('attribute_options', static function (Blueprint $table) {
    $table->id();
    $table->foreignId('attribute_id')->constrained();
    $table->string('value');
    $table->timestamps();
});

With its Model:

app/Models/AttributeOption.php

use Illuminate\Database\Eloquent\Relations\BelongsTo;
// ...
 
class AttributeOption extends Model
{
    protected $fillable = [
        'attribute_id',
        'value',
    ];
 
    public function attribute(): BelongsTo
    {
        return $this->belongsTo(Attribute::class);
    }
}

Now we are ready for our products table:

Schema::create('products', static function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('slug');
    $table->timestamps();
});

And its Model:

app/Models/Product.php

// ...
use Illuminate\Database\Eloquent\Relations\HasMany;
 
class Product extends Model
{
    protected $fillable = [
        'name',
        'slug',
    ];
 
    public function skus(): HasMany
    {
        return $this->hasMany(Sku::class);
    }
}

Next, we'll create our skus table:

Schema::create('skus', static function (Blueprint $table) {
    $table->id();
    $table->foreignId('product_id')->constrained();
    $table->string('code');
    $table->integer('price');
    $table->timestamps();
});

With its Model:

app/Models/Sku.php

// ...
use Illuminate\Database\Eloquent\Casts\Attribute;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;
 
class Sku extends Model
{
    protected $fillable = [
        'product_id',
        'code',
        'price',
    ];
 
    protected function price(): Attribute
    {
        return Attribute::make(
            get: static fn($value) => $value / 100,
            set: static fn($value) => $value * 100,
        );
    }
 
    public function product(): BelongsTo
    {
        return $this->belongsTo(Product::class);
    }
 
    public function attributeOptions(): BelongsToMany
    {
        return $this->belongsToMany(AttributeOption::class);
    }
}

And finally, we'll create our attribute_option_sku pivot table:

Schema::create('attribute_option_sku', static function (Blueprint $table) {
    $table->foreignId('sku_id')->constrained();
    $table->foreignId('attribute_option_id')->constrained();
});

That's it. We now have our database ready for action!

Writing Seeders

We have the DB schema. Let's test it with a few actual products. As an example, I've chosen...