Courses

Structuring Databases in Laravel 11

Custom Fields for E-Shops: EAV Model

Summary of this lesson:
- Understanding EAV model
- Implementing Entity-Attribute-Value pattern
- Comparing JSON vs EAV approaches
- Performance testing different solutions

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...

The full lesson is only for Premium Members.
Want to access all 18 lessons of this course? (81 min read)

You also get:

  • 69 courses (majority in latest Laravel 11)
  • Premium tutorials
  • Access to repositories
  • Private Discord