help regarding commenting system in PHP

Question

Hi there,

I am building a blog like application where an author creates a post and the users without registering comment on the post the comment will be displayed if and only approved by the author, the help i want is regarding the database table, i have created a table which will hold the records such as name, email, phone, location etc. i have defined a new_id as foreign key to hold the id of the news to which it belongs.

is this the correct way of doing it as the commenting system will be built on many to one relationship, or do i need to create another table to define foreign keys ? how do i achieve it?

Answer 1

A one to many relationship can be mapped with a foreign key in one of the tables, or with a junction table.

The way you do it is usually preferred. A user has a newsitem_id, and there can be multiple users to a newsitem.

If you want to store additional information, for example when the newsitem was added, a junction table can be used. Also, if you think that you may want to have multiple users to a newsitem, you need a junction table.

Answer 2

Your approach is basically sound. (Though a better name for new_id would be good.) You do not need an additional table to achieve what you describe. Making new_id a foreign key would be beneficial in that it would enforce referential integrity (ie. if you delete a post, the comments would get deleted along with it.) Do a search here for foreign key and there are several explanations of foreign key.