raygo
raygo

Reputation: 1398

Save array in database or make different columns

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

Answers (1)

Hammerite
Hammerite

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

Related Questions