Apr 14, 2021

Level 5

How granular for Models?

If I have a Product

And a Product can have a number of options - Size and Colour

And a Size can have Small, Medium, Large

And a Colour can be Red, Green, Blue

From a Models perspective how does this work?

In my Database I have the tables - product, product_options and product_options_values

Should I have 3 corresponding Models? I would appreciate input.

bugsysha

5 years ago

Level 61

Assumption is that you use MySQL cause you didn't specify. Following doesn't apply to databases like MongoDB.

If you want to follow relational database architecture then you need following:

products:
- id
- title
- created_at
- updated_at

product_sizes:
- id
- size
- product_id
- created_at
- updated_at

product_colors:
- id
- color
- product_id
- created_at
- updated_at

Columns created_at and updated_at are just an example. No need to have them, but it looked empty without it.

So products that have size will have records in product_sizes table. And products that have colors will have records in product_colors table. It is a relational database. So make everything that is optional a relation. What isn't optional put it into products table.

Ofc, you can have foreign keys instead of color and size columns.

elliotk

5 years ago

Level 5

Thanks @bugsysha - my question wasn't on the mysql database - I am good with that. I was wondering about the Models structure in Laravel itself. It's all related to Products, but is it more desirable to have each table with its own corresponding model?

bugsysha

5 years ago

Level 61

I have absolutely no idea what you are referring to, but what you've described is not proper MySQL architecture.

martinbean

5 years ago

Best Answer

Level 80

@elliotk I’ve worked on a lot of e-commerce websites, and my last role was for a large e-commerce platform vendor in the UK.

In the past, I’ve two “main” models: a Product model (which contains the common details of a product, such as name, description, etc) and then a Sku model, which holds well, the SKU and attributes for a single instance of a product, such as price and inventory.

So in the example of a t-shirt, there would be one t-shirt model but it would then have multiple SKUs for each size and colour combination available for that shirt.

For the options, I model these as an Attribute model. A model can have many attributes (size, colour, etc). I’d then have a pivot table between attributes and SKUs, with the attribute value stored as a pivot column. So there may be rows that looks like this:

+--------------+--------+-------+
| attribute_id | sku_id | value |
+--------------+--------+-------+
| 1            | 1      | blue  |
| 2            | 1      | small |
+--------------+--------+-------+

So this is saying, for the SKU with the ID of 1, the value of attribute ID 1 (colour) is “blue” and the value for attribute ID 2 (size) is “small”.

Modelling attributes like this means that you can find a SKU matching a particular set of attributes values. It also means products can have any number of attributes. So if you started selling say, PCs, then you can add attributes for things like processor, memory, storage, etc and then add a SKU for each combination.

3 likes

elliotk

5 years ago

Level 5

That’s super helpful and exactly the kind of information I was looking for. I’ll take a similar approach.

elliotk

5 years ago

Level 5

Hello @martinbean I am having some trouble with the relationships, hoping you can give me a few pointers...

Here are the tables

products

+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255)        | NO   |     | NULL    |                |
| url        | varchar(255)        | NO   |     | NULL    |                |
| enabled    | tinyint(1)          | NO   |     | NULL    |                |
| tenant_id  | bigint(20) unsigned | YES  | MUL | NULL    |                |
| created_at | timestamp           | YES  |     | NULL    |                |
| updated_at | timestamp           | YES  |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+

skus

+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| tenant_id  | bigint(20) unsigned | YES  | MUL | NULL    |                |
| product_id | bigint(20) unsigned | NO   | MUL | NULL    |                |
| sku        | varchar(255)        | NO   |     | NULL    |                |
| name       | varchar(255)        | NO   |     | NULL    |                |
| price      | decimal(8,2)        | YES  |     | NULL    |                |
| stock      | int(11)             | YES  |     | NULL    |                |
| enabled    | tinyint(1)          | NO   |     | NULL    |                |
| created_at | timestamp           | YES  |     | NULL    |                |
| updated_at | timestamp           | YES  |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+

attributes

+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| tenant_id  | bigint(20) unsigned | YES  | MUL | NULL    |                |
| name       | varchar(255)        | NO   |     | NULL    |                |
| enabled    | tinyint(1)          | NO   |     | NULL    |                |
| created_at | timestamp           | YES  |     | NULL    |                |
| updated_at | timestamp           | YES  |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+

attribute_sku

+--------------+---------------------+------+-----+---------+-------+
| Field        | Type                | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| attribute_id | bigint(20) unsigned | NO   | PRI | NULL    |       |
| sku_id       | bigint(20) unsigned | NO   | PRI | NULL    |       |
| value        | varchar(255)        | NO   |     | NULL    |       |
+--------------+---------------------+------+-----+---------+-------+

Then in my models, I have..

Product

With the following relationships

    public function sku()
    {
        return $this->hasMany(Sku::class);
    }

    public function attribute()
    {
        return $this->hasManyThrough(Attribute::class, Sku::class);
    }

Sku

With the following relationships

    public function product()
    {
        return $this->belongsTo(Product::class);
    }

    public function attribute()
    {
        return $this->hasMany(Attribute::class)->withPivot('value');
    }

And finally Attribute

With the following relationships

    public function sku()
    {
        return $this->belongsToMany(Sku::class);
    }

    public function product()
    {
        return $this->belongsToMany(Product::class);
    }

However, when I try and load the attributes off the Product

$product->load('attribute');

I get the following error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'attributes.sku_id' in 'on clause' (SQL: select `attributes`.*, `skus`.`product_id` as `laravel_through_key` from `attributes` inner join `skus` on `skus`.`id` = `attributes`.`sku_id` where `skus`.`product_id` in (1) and `attributes`.`tenant_id` is null)

What I want to do is get a list of all the Attributes (Name and Value) for a given Product. I guess there are some relationship issues.

Please or to participate in this conversation.