CODEWITHSUNDEEP
phpmysql
user3364397
user3364397

Reputation: 25

Best method to store multiple data for row

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?

Upvotes: 2

Views: 780

Answers (2)

cEz
cEz

Reputation: 5062

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)

Upvotes: 0

juergen d
juergen d

Reputation: 204746

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'

Upvotes: 4

Related Questions