Best method to store multiple data for row

2
votes

I'm thinking about the best method for storing a plurality of values that can be different for each row. For example, I have a table of products and categories. At the beginning of the project one product was assigned to one category. So you could assign products to categories with a single column in the table products. I thought it would do well to assign more than one category to the same product.

In my head I have two ideas.

  1. Create a third table with two columns category_id and product_id.
  2. Do not create another table and for each product to add one or more duplicate row with category name, id and product_id.

Is my thinking is ok and if there is other, more optimal method?

2
phpmysql
Any time you find yourself trying to store multiple values in a single column, stop yourself, remember that doing so violates database normalization and act accordingly, as in your second point. – Niagaradad
The first point is also not about storing multiple values in one column. What do you think about keeping images (URLs) in the form of serialized array in one column? – user3364397

2 Answers

4
votes

You describe a many-to-many relation. Use a third table. That is a common use case. Like this

products table
--------------
id
name
...


categories table
----------------
id
name
...


product_categories table
------------------------
product_id
category_id

Then you can get all categories of a product like this

select c.name
from categories c
join product_categories pc on pc.category_id = c.id
join products p on pc.product_id = p.id
where p.name 'my product name'
0
votes

Depending upon the application, the number of reads/writes, etc, as provided by juergen d you can use a lookup table to store the pairing information.

If you have a limited set of categories (64 or less) then you have the option of avoiding the overhead of the lookup table and use a SET field. It denormalises, but means that you can retrieve the categories along with the product from a single row.

You can then also do bitwise comparison to find products that are in a certain category/set of categories, e.g.

mysql> show create table products\G
*************************** 1. row ***************************
       Table: products
Create Table: CREATE TABLE `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(40) NOT NULL,
  `product_categories` set('a','b','c') DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `product_categories` (`product_categories`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into products set name = 'product a', product_categories = 'a';
Query OK, 1 row affected (0.00 sec)

mysql> insert into products set name = 'product b', product_categories = 'b';
Query OK, 1 row affected (0.01 sec)

mysql> insert into products set name = 'product ab', product_categories = 'a,b';
Query OK, 1 row affected (0.01 sec)

mysql> select id, name from products where product_categories & 1;
+----+------------+
| id | name       |
+----+------------+
|  1 | product a  |
|  3 | product ab |
+----+------------+
2 rows in set (0.00 sec)

mysql> select id, name from products where product_categories & 2;
+----+------------+
| id | name       |
+----+------------+
|  2 | product b  |
|  3 | product ab |
+----+------------+
2 rows in set (0.00 sec)

mysql> select id, name from products where product_categories = 'a';
+----+-----------+
| id | name      |
+----+-----------+
|  1 | product a |
+----+-----------+
1 row in set (0.00 sec)