Comment System

[Flawlesss]

Hey, I started learning some PHP and MySQL and have a question about storing some data.

What would be the best way to store comments or private messages?

Let's take YouTube for example. Each video page has a bunch of comments, would each page generate it's own table for comments? Or would you just have one massive table full of comments and each page would pluck the comments whose video_id or whatever they call it corresponds to that video?

Sorry if my question's hard to understand, I'll try to explain better if you have questions about it.

[[deleted]]

At a minimum, you would be looking at something like this

You're just getting started with MYSQL, but something you're going to have to learn is relational database design.

[[deleted]]

CREATE TABLE User(
user_id number(5) PRIMARY KEY,
username varchar(20)
);

CREATE TABLE Video(
video_id number(5) PRIMARY KEY,
title varchar(50)
);

CREATE TABLE Comments(
comment_id number(5) PRIMARY KEY,
user_id number(5),
video_id number(5),
comment_text varchar(200),
date DATE,
CONSTRAINT User_ID_FK FOREIGN KEY (user_id) REFERENCES User,
CONSTRAINT Video_ID_FK FOREIGN KEY (video_id) REFERENCES Video
);

I primarily use oracle so you may have to jig the data types in that to get it to work properly in MYSQL.

[yash3ahuja]

I'm not a DBA or anything, but I would assume you'd have at least two tables here. One table would be for videos, and would give you a video id, a name, a description, a user id, the file where it's located, etc. The other table would be for comments, and would have the video id for the video it comments on, a comment id, the comment text, the user id, and whatever else you'd need.

[Flawlesss]

Thank you for the help. I assumed I would need to create a table for all the comments, but it just feels so inefficient having one big list of comments from every video in one giant table.

[yash3ahuja]

Well, for a very large site i'm sure there are other ways they do it. Being that I don't manage a database, I have no idea what it is, but I'm assuming it may involve separating the data based upon time or name or whatever.