Save array in database or make different columns

Question

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.

Hammerite · Accepted Answer · 2012-05-01 15:23:04Z

2

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.)

answered May 1, 2012 at 15:23

Hammerite

22.5k6 gold badges76 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

raygo Over a year ago

I didn't want to create a cross-reference table because it's only maximum 3 categories. I thought that for that just to create 3 more columns in my table. Wouldnt that be faster?

Hammerite Over a year ago

Apart from design considerations, once you start writing queries that say either WHERE category_1 = x OR category_2 = x OR category_3 = x, or (god forbid) queries whose WHERE clause requires parsing a comma-separated list, I very much doubt it would be faster than using a cross-reference table.

raygo Over a year ago

Alright! I guess that's what I will do! I was just scared of the performance of joins, thought it was going to be slower. Thanks!

Hammerite Over a year ago

Joins using primary keys are very fast, do not worry about them.

Collectives™ on Stack Overflow

Save array in database or make different columns

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related