1

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.

raygo
  • 1,348
  • 5
  • 18
  • 40

1 Answers1

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

Hammerite
  • 21,755
  • 6
  • 70
  • 91
  • 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? – raygo May 01 '12 at 15:24
  • 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. – Hammerite May 01 '12 at 15:28
  • 1
    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! – raygo May 01 '12 at 15:35
  • Joins using primary keys are very fast, do not worry about them. – Hammerite May 01 '12 at 15:37