Structuring Product Attributes

Hello, I suggest you use this pattern

Create Product table with the following column (id, name, ..... )

Create ProductAttribute table with the following column (id, productId, color, size,........ )

Note you can make columns in ProductAttribute to be nullable but never unique. This is to allow a single product to have multiple attributes

@fahaddsheikh Hey! So I worked on quite a few e-commerce projects. How you structure attributes depends on if you want them to reusable or not.

If you want attributes to be reusable, them you could have an Attribute model and an AttributeValue model like you mention. You would then create essentially a three-way pivot table between your products, attributes, and attribute values:

Schema::create('channel_user', function (Blueprint $table) {
    $table->primary(['product_id', 'attribute_id']);
    $table->foreignId('product_id')->constrained()->cascadeOnDelete();
    $table->foreignId('attribute_id')->constrained()->cascadeOnDelete();
    $table->foreignId('attribute_value_id')->constrained()->cascadeOnDelete();
});

There’s a compound primary key ensuring that the product can only have an attribute once. The columns are then just foreign keys pointing to the other tables.

With this setup, you can then retrieve all the attributes for a product, and also the value using the pivot column:

class Product extends Model
{
    public function attributes()
    {
        return $this->belongsToMany(Attribute::class)->withPivot('attribute_value_id');
    }
}

Accessing attribute_value_id will yield the primary key of the value model only though. You may want to create a custom pivot model so you can access attributes on the related AttributeValue model as well. Then you could do something like:

foreach ($product->attributes as $attribute) {
    // $attribute->pivot would be pivot model

    $attributeName = $attribute->name; // i.e. Color
    $attributeValue = $attribute->value->name; // i.e. Red if AttributeValue model has name column
}