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