How do you manage a list for particular object. for eg. user comments on specific object(user post etc.)

Question

I am new to database and web application development. I want to know how to store a list for a particular object in a database. For example, user comments for a particular post(object), or the friends or followers list of a particular user.

Normally we save data in the form of row

|user_id  | user_name | use_password | etc | etc
|123      | neha      | 123          | ... | ...

but here you cannot save them in rows because its a list:

|post_142524 | comment1 | comment2 | comment3 | etc...

How do you store comments? I can't think of creating new columns for each comment. and I can't create a table which stores data column-wise like this:

|post_142524|
|comment1   |
|comment2   |
|comment3   |
|comment4   |
|comment5   |
etc...

I want to know how to manage lists for particular object. I've searched around but, I think my search terms were not perfect to get what I am looking for, as I don't even know what term to use to search for this problem. Any help would be appreciated!

Answer 1

As the comments indicate, you need to understand normalization. Without too much theory, when you want a list for a particular object in a database here are some possibilities:

• Create a new column for each additional list item. If your list has a small concrete length this may be a viable option, however for most lists n items today becomes n+1 tomorrow and your table begins to have an unmanageable number of columns that require extra coding for joining and filtering.

• Create a new table for each additional list. If the lists are independent then this is the way to go, however, if the lists are the same, then creating and maintaining a new list table for each and every object becomes unmanageable. Additionally, queries crossing objects require UNIONs on multiple tables. These queries could become lengthy and have to be modified with each additional object.

• Create a normalized table for this type of list. If the list structure is the same for each object then a single table can be used to store any number of list items for any number of objects. The tables definition should be defined and scoped.

For your particular question you have defined the list item as a comment, so you will simply need additional columns to indicate what object (post?) it goes with and what comment number it is. You may also want to have columns indicating who made the comment, when it was made, and from where it was made.

Here is how this could look:

Post    CommentNumber  Comment                                     
------  -------------  --------------------------
098763  1              Comment 1 for post 098763.
...  
142525  1              Comment 1 for post 142525.
142525  2              Comment 2 for post 142525.
...
200526  1              Comment 1 for post 200526.