How to design a base for an online store?

Question

Database design

Something more complicated than a blog with comments and categories has never been designed. For the sake of education, design issues are of interest.
How to properly design a database for an online store with many categories, in which there should be a different filter system, since products have different attributes, sometimes they overlap between categories (product weight), and sometimes not.
The most primitive, as I understand it, is the creation of a table for each category, where the columns are attributes.
But then, a product cannot be present in several categories, and if brands intersect, then in order to make a selection by the brand of all products, you need to go into all tables and filter by brand? In general, there are still more questions.
Another googled option, EAV - Entity Atribute Value. It seems that everything is logical and correct, the category can be an attribute, the name of the category can be a value, the same with brands, the brand is an attribute, the name is a value. Magento CMS works according to this model, the number of tables there is much larger of course. It is also googled that this is an anti-pattern and very bad and slow.
Tell me what are the options for designing e-commerce, in Google and in the Gita I meet either options that are very difficult to understand or a primitive where a product cannot be added to several categories. I would like to understand the pros and cons of one or another option. It's not that I want to create Amazon, but for the sake of education.
And how difficult or easy it will be to use in Laravel ORM.
There may be some tutorials on developing a more or less flexible store in Laravel.

Answer 1

MongoDB or jsonb in PostgreSQL and no design pain.

Answer 2

EAV is insanely slow. For storage and editing - convenient , logical.
But giving information from it to user requests is bad, slow .
Specialized faceted search DBMSs are better suited for working with filters, rather than classic relational DBMSs like MySQL.
Faceted (and at the same time full-text) search is, for example, SphinxSearch, ManticoreSearch, ElasticSearch, etc.

Answer 3

This is hardly the correct pattern, but I did this:
The table has a bunch of columns of the form v1, v2, v3, v4 (varchar), i1,i2,i3 (int)......
For each category, an array of links of the 'color' type => 'v2', 'radius' => 'i1'
In models through setters, getters and scopes, all this is converted into a human-readable form.

Answer 4

If I understand the question correctly, intermediate tables can be used.
Tables:
ITEM
id | name
1 | t-shirt
2 | polo shirt
3 | jeans
CATEGORY
id | name
1 | top
2 | bottom
ATTRIBUTE
id | name
1 | size
2 | inseam
3 | waist
ITEM_CATEGORY
id | item_id | category_id
1 | 1 | 1
2 | 2 | 1
3 | 3 | 2
ITEM_ATTRIBUTE
id | item_id | attribute_id | value (or value_id if attributes are defined)
1 | 1 | 1 | XL
2 | 2 | 1 | M
3 | 3 | 2 | 34
4 | 3 | 3 | 32
In practice, polymorphic relationships are also likely to be useful, when references to rows belonging to different tables can be stored in one column. To do this, a type column is added to the source table, where the type is stored (which table to access), and the id of the row in this table is stored in another column.
You can already add a type column to the ITEM_ATTRIBUTE table to store links to rows in different tables, where attribute values ​​are listed (for example, add the TOP_SIZE_VALUE table and shove not a row, but a link) and rows.