Custom Fields for E-Shops: EAV Model

There are situations when we're not sure what the columns of the DB table would be, they need to be flexible. For example, e-shop product properties: size, color, fabric, there may be more in the future. What is the best DB structure? I will show you three options and their performance.

Option 1. Properties DB Table and EAV Model

We can define a separate DB table properties and then a Pivot table product_property with many-to-many relations, and an extra field for the value.

This DB structure implements the so-called EAV (Entity-Attribute-Value) Model.

database/migrations/xxx_create_product_property_table.php:

Schema::create('product_property', function (Blueprint $table) {
    $table->foreignId('product_id')->constrained();
    $table->foreignId('property_id')->constrained();
    $table->string('value');
});

app/Models/Product.php:

class Product extends Model
{
    public function properties()
    {
        return $this->belongsToMany(Property::class)
            ->withPivot('value');
    }
}

Then, if we want to filter the products by specific few property values (like, "Size L and color red"), we can do this.

Controller:

Product::query()
    ->whereHas('properties', function($query) {
        $query->where('properties.name', 'size')
            ->where('product_property.value', 'L');
    })
    ->whereHas('properties', function($query) {
        $query->where('properties.name', 'color')
            ->where('product_property.value', 'red');
    })
    ->get();

Notice: you may skip the ::query(), I add it personally to have the well-formatted following lines of code, for better readability.

This structure gives you the flexibility to add...