Reputation: 1398
I'm creating a project where I will let somebody choose maximum 3 categories from 20. Because it's always going to be maximum 3 categories, it's not that much for me to just create 3 more columns in the database (category_1, category_2, category_3).
I imagine in the time of insert, instead of saving in 3 different columns, to just save the array into a "categories" column.
My question is, in the moment of getting the information. If I do let's say a search for each record that has 1 specific category, what would be faster? For it to look into 1 column that is an array ( I imagine with a LIKE or something like that ) or to look in 3 columns?
I hope my question is clear. Thanks.
Upvotes: 1
Views: 1179
Reputation: 22340
Don't store a comma-separated list ("array") in a database column. Use a cross-reference table.
(If you want to enforce the "no more than 3 categories" condition in the database, you can add an additional column category_number
to the cross-reference table. Ideally you would then use a check constraint to restrict that column to the values 1, 2 and 3, but MySQL does not support check constraints; instead you might make the category_number
column a foreign key to another table permitted_category_number
which has three rows.)
Upvotes: 2