Janusz Kubala Janusz Kubala - 6 months ago 9
MySQL Question

Storing assignments between 2 tables in MySQL

I am wondering what is the best solutions to store relations between 2 tables in mysql.

I have following structure

Table: categories

id | name | etc...
_______________________________
1 | Graphic cards | ...
2 | Processors | ...
3 | Hard Drives | ...

Table: properties_of_categories

id | name
_____________________
1 | Capacity
2 | GPU Speed
3 | Memory size
4 | Clock rate
5 | Cache


Now I need them to have connections, and question is what is a better, more efficient and lighter solution, which is important because there may be hundreds of categories and thousands of properties assigned to them.

Should I just create another table with a structure like

categoryId | propertyId


Or perhaps add another column to categories table and store properties in text field like 1,7,19,23

Or maybe create json files named for example 7.json with content like

{1,7,19,23}

Answer

The first solution is better when it comes to relational databases. You should create a table that will pair each category to multiple properties (1:n relationship)

You could structure the table like so:

CREATE TABLE categories_properties_match(
    categoryId INTEGER NOT NULL,
    propertyId INTEGER NOT NULL,
    PRIMARY KEY(categoryId, propertyId),
    FOREIGN KEY(categoryId) REFERENCES categories(id) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY(propertyId) REFERENCES properties_of_categories(id) ON UPDATE CASCADE ON DELETE CASCADE
);

The primary key ensures that there will be no duplicate entries, that means entries that match one category to the same property twice