Representing lists in SQL

My database setup is modern (My)SQL.
I've got a Content Table, including ids.
Now, users should be able to comment on content.
To achieve this, arrays have come to my mind.
Now I see several options of representing them :

• A second table Comments with Content id and Comment

• One new table for each Content, to store the comments(advantage : no need to store content id again for each comment)

• A Text/String column, using a certain separator to seperate comments & comment infos

• Same as above, but using a BLOB

Which one should I use ? What other options haven't I thought of ? Thanks for taking your time to answer !

In terms of relational databases each table represents a type. Table Content contains the items of content type. Table Comment contains the items of comment type etc. There is no strict requirement like "normalization" to keep all the items of the same type in the same table. Even more - sometimes you have to do that for sake of performance. But having multiple tables of the same type requires all that tables to be modified simultaneously. As of experience that requirement is very easy to violate by mistake. Now I'm trying to avoid this at any cost.

Sure if you have to get "the latest comment for [some|all] content" frequently the table of the same structure as Comment but with UNIQUE constraint combined with INSERT .. ON DUPLICATE KEY UPDATE .. (often acronymed by IODKU) is way more faster than any joined subqueries. But this is the special case.

So in my opinion the first option is much better than second.

And the third and fourth options requires some redundant homemade storage engine over the relational database that should parse string or blobs for fetching the data. This approach insults not only performance but also the reliability and maintanability of the storage. This is the sort of the bad design that should be avoided.

StackExchange Database Administrators Q#218311, answer score: 3