Relationship or column?

If you need more data to put related to every attribute then go with separate models. If not then go with single table.

@johncarter Have a ProductAttribute model and a pivot table between this and your product model. The pivot table can contain the value for a product and attribute combination, and if you create a compound primary key from the product ID and attribute ID, that will stop you from being able to assign the same attribute multiple times to a single product.

Thanks both, I am going to try the @martinbean way and see how I get on.

To confirm I would have a pivot that looks like this:

Schema::create('product_product_attribute', function (Blueprint $table) {
    $table->unsignedBigInteger('product_id');
    $table->unsignedBigInteger('product_attribute_id');
    $table->string('value'); // "denim_blue"
    $table->string('name'); // "Denim Blue"
    $table->primary(['product_id', 'product_attribute_id'], 'product_product_attribute');
});

If you need more data to put related to every attribute then go with separate models. If not then go with single table.

Forgot to mention if you are using Laravel 7, use custom attribute casting.

https://laravel.com/docs/7.x/eloquent-mutators#custom-casts

@bugsysha So what you're saying is that I could use a column on the products table then make an accessor for the value?

e.g.

// Product.php
public function getColourAttribute($value)
{
    // $value = 'denim_blue';
    return ucwords(str_replace('_', ' ', $value); // "Denim Blue"
}

Nope. Have you clicked the link? Here is better example if that is confusing to you.

https://laracasts.com/series/whats-new-in-laravel-7/episodes/3

Wow 🤯 that video is really useful, thanks. I just wonder how that will affect query performance. But I'll have a proper look into it.

I just wonder how that will affect query performance.

When that starts being a performance problem, you will have a full team working on a solution for it while you are fusing your ass with the chair and not giving a damn.

@bugsysha I should have actually asked how do I run a query on the column when the data is stored as a cast?

If I understand properly you are proposing that I should to cast the column value as a json string like:

{"handle":"denim_blue","name":"Denim Blue"}

Then if I want to query that column, I do something like this?

$denimBlueProducts = Products::where('colour->handle', 'denim_blue')->get();

See: https://laravel.com/docs/7.x/queries#json-where-clauses

Then to access the property I can do:

{{ $denimBlueProducts->first()->colour->name }}

Am I on the right track here?

No, I'm not suggesting one JSON column, but you can do that also.

You can query it like this:

->whereJsonContains('nameOfTheColumn', ['color' => 'black'])

If it makes sense to stuff everything in one column cause you do not have an idea of how that data will be changed in the future then do it as a single JSON column.

If you know that all those attributes will remain over a long period then create column for each of those attributes cause it is way more readable.