3

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 !

  • Are you going to support nested comments? – akuzminsky Sep 22 '18 at 16:55
  • @akuzminsky Not ATM. – LMD Sep 22 '18 at 17:54
3

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.

  • Well I don't have to get the latest comment, I want to get all comments for some content ;) and that only when a user wants to see it :) – LMD Sep 22 '18 at 12:15
2

Second table for comments is the best option, as you can have many comments by different users at different time interval, which need paginated output during the display.

Separate table for each content is bad idea not only from database side, but also how you are going to identify each content uniquely from frontend itself, so some form of ID is must have need.

Storing comments data in coloumn is too bad. You will be end up having different size of rows for table and moreover you need to perform all operations on comments within your application itself. This will result in poor performance of your application.

  • So if I got you right you are for the 1st option ? – LMD Sep 22 '18 at 11:34
1

I vote for 2 tables, but probably not in any of the ways you suggested.

Threads. Have a table for Threads -- one row for each thread, containing metadata about the thread, but none of the text.

Q&A. Have a second table for each item per thread. If it is a simple list of chatter in a thread, then this is the only other main table.

Comments. If you have a complex situation, such as this stackexchange forum, you probably need a third table.

Notice how the Question and Answer are quite similar in structure, hence both go in the second table. But the Comments have a different flavor, a different set of things that can be done, etc. So they really need to have a third table.

I have seen some people try to fold the Threads into the Q&A table. This makes a mess, and should be avoided.

As for one-dimensional versus tree-like, either can be handled with the 2 or 3 tables. There is an id and a parent_id in any tree-like table. parent_id is 0 for the 'head' ('root') of the tree. It points to an id otherwise. (Displaying output is a UI challenge, not a database challenge.)

Do not throw a bunch of 'identical' things into a single cell. Use another table so they can be one-per-row.

Do not splay an array across multiple columns. Ditto, use another table with rows.

1

Your first option seems the best to me:

  • A second table Comments with Content id and Comment

Why?

Let's look at why the other options are less optimal.

Your second option:

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

Now your code has to specify a different table each time you want to read comments for a different content type:

if ($contentType = 1) then $sqlPart = 'table1';
elseif ($contentType = 2) then $sqlPart = 'table2';
else $sqlPart = 'table3';
$sql = 'select comment from ' + $sqlPart;

This is not sustainable. Your content table contains data - your system can add new data, then you have to re-write your program code to account for the new data (as well as create a new comments table for the new content type).

Your third option:

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

Now you have to do a whole lot of processing to get comments:

$comments = getCommentsFromWideField();
$commentArray = split($comments);

Not only is this a lot of work - but how do you sort comments into time order? You have to encapsulate the comment timestamp into your wide string. Your super-super wide column is also not efficient within the database. How will you update a comment - extract all of the comments, break up the string into an array, somehow locate the one comment you want to edit, edit it, put the array back into a string and update your huge column. No thanks.

Your fourth option:

  • Same as above, but using a BLOB

My response is same as above too.

There are good reasons why normalised databases are so popular - these include efficiency and ease of understanding relationships between data. With a dedicated table for comments you will be able to extend your data model easily in future, for example, to include new information about each comment record: time created; user who commented; upvotes; tags; etc.

Go with two tables: Content and Comments. Join them on the id that you have. Your future self will thank you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.