Janusz Kubala Janusz Kubala - 2 years ago 59
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


Answer Source

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(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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download