Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
add comment

1 Answer

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.
share|improve this answer
    
is it a standard way of storing comments? I mean storing all the comments on my website in single table. Or can i create separate table for each post, or are there any other ways. How others do it? please can you explain me in detail. thanks –  Neha Rastogi Sep 4 '12 at 16:05
    
The above example is referred to as a "normalized" database design. If you do a search for "database normalization" you will learn all about how to solve your problem! –  Max Vernon Sep 4 '12 at 16:14
    
I read lilbit about normalization right now, but it doesn't relate to my question in any manner. –  Neha Rastogi Sep 4 '12 at 16:34
    
If you know any blogs or any other website that explains about my problem, then please comment with the link...please. highly need to know about this matter –  Neha Rastogi Sep 4 '12 at 16:36
1  
If normalization doesn't relate to your problem in any manner, then you are not comptent to be designing a database. –  HLGEM Sep 4 '12 at 17:49
show 5 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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