pointers for most efficient method?

[android6011]

I'm going to have a mysql database, and in one table its going to look like this (maybe)

 

TABLE NAME: Articles

 

id|user|date|catagory|article title|article

 

but i also want to add a comment system, so should i have a separate table that is like

 

TABLE NAME: COMMENTS

 

id|article id| user| comments

 

and then just list the article titles in the articles table on one page and then on another display a full article then get the id and grab the comments?

 

i know this probably is about as unefficient and crazy as it gets but im not sure how to go about storing the information. thanks

[quiettech]

It's not inefficient. It a right way to do it.

 

Just make sure the Comments table primary key is a composite of id+article_id and don't forget to create a foreign key relationship between comments.articlei_d and articles.id.

 

With this you create a 0-to-many relationship between articles and comments.

[aschk]

What aren't you sure about?

It appears you know what database system you are using. And you have listed the table layout, so you know what columns there are. The only thing I can see is that you haven't specified what datatypes the columns are. What else do you need to know, as the layout looks pretty much perfect to me, and you clearly have a good idea about how it's going to function.

[android6011]

the only thing im not sure about doing is like say i have 10 articles and 1000 comments it could take a while to search for the article_id through all those comments. and increasingly longer everytime a comment is added.

 

Just make sure the Comments table primary key is a composite of id+article_id and don't forget to create a foreign key relationship between comments.articlei_d and articles.id.

 

this is kind of what i thought i should do but im not 100% sure on how to create the relations. right now i have no tables created or anything so how would i create a table with my layouts and your suggestions just to be sure im doing it right? thanks

[quiettech]

Gives me something to work and I will help.

Just don't ask me to do it for you.

[android6011]

CREATE TABLE articles(user VARCHAR(10), date VARCHAR(10), catagory VARCHAR(10),title VARCHAR(20), article MEDIUMBLOB NOT NULL)

CREATE TABLE comments (PRIMARY KEY (id, article_id) ,FOREIGN KEY (article) REFERENCES articles(id))

 

??

[android6011]

bump

[android6011]

anyone have any suggestions on creating the tables?