Mysql – How to make db tables for product filters?

A database is normalized, at least in part, when there’s a single column for each type of information. So when you see more than one column having the same information, that part of the database is not "normalized".

Similarly when you see lists of data in a column, such as red, blue, purple in a single column, this is a candidate for normalization. (Not to say there is no place for "un-normalized" data in a relational database.)

1. Items
| Id | Item_Name   | Cat_Id |
| 1  | Samsung S20 | 1      |
| 2  | iPhone 5    | 1      |
| 3  | T-shirt     | 2      |

2. Item_Color_Options
| Item_Id | Color  |
| 1       | blue   |
| 1       | red    |
| 2       | blue   |
| 3       | purple |

3. Item_Processor_Options
| Item_Id | Processor |
| 1       | Intel     |
| 1       | AMD       |
| 2       | Intel     |

Another opportunity for normalization is when a column has the same data repeated in more than one rows, such as:

| Item_Id | Color  |
| 1       | blue   |
| 1       | red    |
| 2       | blue   |
| 3       | purple |

// and

| Item_Id | Processor |
| 1       | Intel     |
| 1       | AMD       |
| 2       | Intel     |

…where blue and Intel are repeated.

In this case, and particularly when the same value or option may be applied to more that one item, the duplicated options can be eliminated using linking tables:

// primary tables

1. Items
| Id | Item_Name   | Cat_Id |
| 1  | Samsung S20 | 1      |
| 2  | iPhone 5    | 1      |
| 3  | T-shirt     | 2      |

2. Categories
| Id | Cat_Name | 
| 1  | mobiles  | 
| 2  | clothes  | 


// option tables

3. Color_Options
| Id | Color       |
| 1  | red         |
| 2  | blue        |
| 3  | purple      |

4. Processor_Options
| Id | Processor       |
| 1  | Intel           |
| 2  | AMC             |


// linking tables

5. Item_Color_Options
| Item_Id | Color_Id |
| 1       | 1        |
| 1       | 2        |
| 2       | 1        |
| 2       | 3        |

6. Item_Processor_Options
| Item_Id | Processor_Id |
| 1       | 1            |
| 2       | 2            |
| 3       | 1            |

4. Cats_Opts
| Id | Cat_id | Option_id |
| 1  | 1      | 1         | 
| 2  | 1      | 2         | 

To compile all the options into fewer reference tables:

Option_Types
| Id | Option    |
| 1  | Color     |
| 2  | Processor |
| 3  | Other     |

Options_Values
| Id | Option_Types_Id | Value       |
| 1  | 1               | red         |
| 1  | 1               | blue        |
| 1  | 1               | purple      |
| 2  | 2               | Intel       |
| 2  | 2               | AMD         |

Item_Options
| Item_Id | Option_Values_Id |
| 1       | 5                |
| 2       | 4                |
| 3       | 2                |

Then, of course, JOIN them all together.

-- create
CREATE TABLE Items (
  Id INTEGER PRIMARY KEY,
  Item_Name TEXT NOT NULL,
  Category_Id INTEGER NOT NULL
);

CREATE TABLE Categories (
  Id INTEGER PRIMARY KEY,
  Cat_Name TEXT NOT NULL
);

CREATE TABLE Option_Types (
  Id INTEGER PRIMARY KEY,
  `Option` TEXT NOT NULL
);

CREATE TABLE Option_Values (
  Id INTEGER PRIMARY KEY,
  Option_Types_Id INTEGER NOT NULL,
  Value TEXT NOT NULL
);

CREATE TABLE Item_Options (
  Items_Id INTEGER NOT NULL,
  Option_Values_Id INTEGER NOT NULL
);

-- insert
INSERT INTO Items VALUES (1, 'Samsung S20', 1), (2, 'iPhone 5', 1), (3, 'T-shirt', 2);
INSERT INTO Categories VALUES (1, 'mobiles'), (2, 'clothes');
INSERT INTO Option_Types VALUES (1, 'Color'), (2, 'Processor');
INSERT INTO Option_Values VALUES (1, 1, 'red'), (2, 1, 'blue'), (3, 1, 'purple'), (4, 2, 'Intel'), (5, 2, 'AMD');
INSERT INTO Item_Options VALUES (1, 5), (2, 4), (3, 2);

-- fetch 
SELECT Items.Id, Items.Item_Name, Categories.Cat_Name, Option_Types.`Option`, Option_Values.Value
  FROM Items
  LEFT JOIN Categories ON Items.Category_Id = Categories.Id
  LEFT JOIN Item_Options ON Items.Id = Item_Options.Items_Id
  LEFT JOIN Option_Values ON Item_Options.Option_Values_Id = Option_Values.Id
  LEFT JOIN Option_Types ON Option_Values.Option_Types_Id = Option_Types.Id
;

Outputs:

Try it here: https://onecompiler.com/mysql/3yj627ggs