insert reference in comment table that joins two sessions (blog and news)

Question

I am finalizing a comments system and was with a doubt.

I have a table for blogs and one for news, and they accept comments.

My comments table receives the text and the id. I wonder if I need to (or should I) go through some sort of reference to know where the comment comes from.

table comment

id | id_content | text | ref
1  | 1          | test | blog
2  | 1          | test | news

thanks

Of course you need to add another field which shows whether the comment is for news or blog.Otherwise how will you recognize it.? — 웃웃웃웃웃, Dec 22 '13 at 05:28
would be displayed by ID or news blog with a simple select: **SELECT * FROM Comments where id = $id** — Papa Charlie, Dec 22 '13 at 05:35
But both tables may contain the same id at least at some point of time ... — Nouphal.M, Dec 22 '13 at 05:41
true, but the ID was to illustrate, I use a combination that ensures a unique identifier. — Papa Charlie, Dec 22 '13 at 05:47
Take a look at [Implementing comments and Likes in database](http://stackoverflow.com/a/8113064/533120) for some ideas. — Branko Dimitrijevic, Dec 22 '13 at 15:19

score 1 · Accepted Answer · answered Dec 22 '13 at 06:37

1

depending on the number of comments you expect to receive there are two ways of doing this ...

1 - parent_tbl, parent_id - in one big comment table

2 - two tables for comments with a parent_id - one for each primary table

either way you need to index properly, the second will always work faster, but it doesn't expand well if you say add "press_releases" now you have to duplicate code, tables, what not.

answered Dec 22 '13 at 06:37

cmorrissey

8,493
2
23
27

I am deciding the model #1. tables are identical, do not think there is reason to split in two. IDs do not collide, I can discard **parent_tbl** or should I have her? thank you – Papa Charlie Dec 22 '13 at 07:01
if ids don't collide im assuming the main data is a common table, thats great! you can now get comments with a single WHERE. Now you just need to index index index! if you want to post more of your table structure we can suggest indexes but mainly index your id, your parent/foreign id and anything else you are sorting or selecting on. it can get complicated but when you are dealing with thousands of entries you will be happy you did so. – cmorrissey Dec 22 '13 at 07:16
**I)** I have not done the structure, I start with the best way to not make mistakes. My comment system is simple, so it can be grouped into a single table and use as a simple widget. **II)** The next step is to make the system based on the reply comments, my idea is to make a tbl_reply equal to tbl_comment and receive the ID of the comment - make sense? thanks for the help – Papa Charlie Dec 22 '13 at 07:34
no no, group them all as one, with a `parent_id` as the over arching theme, that way replys could be 2 to a hundred levels deep – cmorrissey Dec 22 '13 at 07:36
save replies in tbl_comment together? then would my table: `id | id_content | text | parent_id_reply` – Papa Charlie Dec 22 '13 at 07:40

insert reference in comment table that joins two sessions (blog and news)

1 Answers1