mysql structure for posts and comments

Question

I've read several tutorials, documentations about mysql, db structures and also I'm using it via php for weeks. Now I get a problem, I don't know how to form/organize/create my db structure for posts and comments. I've already read some posts about this (here on stackoverflow), but I didn't find anything useful. I understand that I need to have 2 tables for posts and comments, and when I need to print them on the page with a foreign key (or ID) I "merge" them (only on the page, not with SQL). When a person is viewing the page, he is seeing the post and comments normally, but in the "background" everything is stored in 2 tables.

Am I needing to add a new column every time when somebody is adding a new comment or a reply?

If my question is true, that means if in a post are 100+ comments, that means I need to ALTER the TABLE every single time? That means if post "A" has 3 comments and post "B" has 150 comments, my table "comments" will have 100+ columns?

E.g:

Posts | column1 | column2 | ... | columnN

A | bla1 | bla2 | bla3 | - empty | - empty | ... | - empty - |

B | bla1 | bal2 | bla3 | bla4 | bla5 | bla6 | ... | bla100 |

No. 'alter table' should be a very rare thing, responding only to changes in "business needs". Google for "database normalization" (and general RDBMS theory) to learn how/why to structure your DB better. — Marc B
No you have say a CommentID and CommentContent and add another row each comment. — Tony Hopkinson
I agree with @Marc B ... however the quick answer is a structure like: POSTS(id,title,text);COMMENTS(id,postid,text);. Each post has a row in POSTS, each comment has a row in COMMENTS. You know which comments are for which posts by the use of comments.postid which matches the posts.id of the post. — Rudu

David David · Accepted Answer · 2012-01-10T18:00:06

At a basic level, you would have a table for each type of "thing" in your application. In this case, a table for Posts and a table for Comments. Something as simple as this:

Post
--------
Id
Content
User
DatePosted

Comment
--------
Id
PostId
Content
User
DatePosted

This would create what's called a one-to-many (or zero-to-many, actually) relationship between Posts and Comments, whereby each Post can have zero or more associated Pomments but each Comment can be associated with only one Post.

In your code (which is a whole other subject entirely), to display a Post and its associated Comments there are a couple of things you could do. Assuming you have, as input, the Id of the Post you want, you can get that Post and its Comments:

SELECT `Content`, `User`, `DatePosted` FROM `Post` WHERE `Id` = ?Id
SELECT `Id`, `Content`, `User`, `DatePosted` FROM `Comment` WHERE `PostId` = ?Id

What you do with that resulting data is up to you and how you want to use it in your application. It would come back as two table results, the former of which has one record (if the Post exists) and the latter of which has zero or more records. Naturally, you'll want to check that things exist before trying to use them, etc. (So if the first query returns no results, don't try to continue to display the Post. Just show a default response or an error.)

mysql structure for posts and comments

1 Answers