There are two tables. One table is a product, the second category of goods. A product may have no more than 3 categories. What is the best way to store it in a database? make the connection between products and categories one to many - one product, 3 categories. and in the table of goods to store the id of the categories to which they belong, but the question is how? create 3 columns just for this not very much. Another option is to create an intermediate table in which the product id will be stored | id categories. Maybe there are some other options?

  • The second option, with a separate table product category. And with the search it is simpler and the groundwork for the future, what if someone wants to add the 4th category - Mike

1 answer 1

The most universal solution will be with an intermediate table for storing links between products and categories. For such a solution there is a special name - the neighborhood table (Closure Table)

CREATE TABLE categories_products ( id int(11) NOT NULL AUTO_INCREMENT, product_id int(11) NOT NULL, catalog_id int(11) NOT NULL, PRIMARY KEY (id) );

You can index foreign key for products and catalogs. It is very easy to delete and edit the links of products and catalogs in an intermediate table. Over time, the table can be indexed to speed up queries or enter into it an additional field for sorting product categories, if the order of following categories suddenly becomes important.