MySQL: One row or more?

Question

I have any kind of content what has an ID now here I can specify multiple types for the content.

The question is, should I use multiple rows to add multiple types or use the type field and put there the types separated with commas and parse them in PHP

Multiple Rows

`content_id` | `type`

1            | 1
1            | 2
1            | 3

VS

Single Row

`content_id` | `type`

1            | 1,2,3

EDIT

---

I'm looking for the faster answer, not the easier, please consider this. Performance is really important for me. So I'm talking about a really huge database with millions or ten millions of rows.

Answer 1

Use multiple rows. That way, you can index that type column later, and search it faster if you need to in the future. Also it removes a dependency on your front-end language to do parsing on query results.

Answer 2

I always use multiple rows. If you use single rows your data is hard to read and you have to split it up once you grab it from the database.

Answer 3

Normalised vs de-normalised design. usually I would recommend sticking to the "multiple rows" style (normalised) Although sometimes (for performance/storage reasons) people deliberately implement "single row" style.

Have a look here:

http://www.databasedesign-resource.com/denormalization.html

Answer 4

I'd generally always recommend the "multiple rows" approach as it has several advantages:

• You can use SQL to return for example WHERE type=3 without any great difficulty as you don't have to use WHERE type LIKE '%3%', which is less efficient
• If you ever need to store additional data against each content_id and type pair, you'll find it a lot easier in the multiple row version
• You'll be able to apply one, or more, indexes to your table when it's stored in the "multiple row" format to improve the speed at which data is retrieved
• It's easier to write a query to add/remove content_id and type pairs when each pair is stored separately than when you store them as a comma seaparated list
• It'll (nearly) always be quicker to let SQL process the data to give you a subset than to pass it to PHP, or anything else, for processing

In general, let SQL do what it does best, which is allow you to store the data, and obtain subsets of the data.

Answer 5

The single row could be better in a few cases. Reporting tends to be easer with some denormalization is the main example. So if your code is cleaner/performs better with the single row, then go for that. Other wise the multiple rows would be the way to go.

Answer 6

Never, ever, ever cram multiple logical fields into a single field with comma separators.

The right way is to create multiple rows.

If there's some performance reason that demands you use a single row, at least make multiple fields in the row. But that said, there is almost never a good performance reason to do this. First make a good design.

Do you ever want to know all the records with, say, type=2? With multiple rows, this is easy: "select content_id from mytable where type=2". With the crammed field, you would have to say "select content_id from mytable where type like '%2%'". Oh, except what happens if there are more than 11 types? The above query would find "12". Okay, you could say "where type like '%,2,%'". Except that doesn't work if 2 is the first or the last in the list. Even if you came up with a way to do it reliably, a LIKE search with an initial % means a sequential read of every record in the table, which is very slow.

How big will you make the cram field? What if the string of types is too big to fit in your maximum?

Do you carry any data about the types? If you create a second table with key of "type" and, say, a description of that type, how will you join to that table. With multiple rows, you could simply write "select content_id, type_id, description from content join type using (type_id)". With a crammed field ... not so easy.

If you add a new type, how do you make it consistent? Suppose it used to say "3,7,9" and now you add "5". Can you say "3,7,9,5" ? Or do they have to be in order? If they're not in order, it's impossible to check for equality, because "1,2" and "2,1" will not look equal but they are really equivalent. In either case, updating a type field now becomes a program rather than a single SQL statement.

If there is some trivial performace gain, it's just not worth it.