How to make db tables for product filters?-CodePudding

I want to create product filter for multiple categories. User will select for ex. Mobile phones category via form select element and then there will be showed options for this category which user can choose, for ex. Color, processor,...

And I want to know how best way how to set up database tables for this purpose. My idea is to create three tables:

1. Items
Id | Item_Name   | Cat_Id | Option_1 | Option_2 | ... 
1  | Samsung S20 | 1      | blue     | Intel    | ...
2  | iPhone 5    | 1      | red      | AMD      | ...
3  | T-shirt     | 2      | red      | NULL     | ...

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

3. Options
Id | Option    | Values                 |
1  | color     | red, blue, purple, ... |      
2  | processor | Intel, AMD, ...        |

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

Problem is, that in my structure many fields will be empty bcz for ex. clothes category will not have value for option_2.

Is this good way how to do db structure for this purpose?

CodePudding user response：

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         |