Database 1:N tables structure, two approaches (one or multiple tables) -

let's assume have application pages, posts , events. each part of application want have comments. let's take tables our db.

1. 1 comment table, object , object_id foreign key

page/post/event has many comments, foreign key object, object_id

 comments table +-------------+-------------+-------------+-------------+ | id          | object      | object_id   | text        | ========================================================= | 1           | page        | 1           | comment 1   | +-------------+-------------+-------------+-------------+ | 2           | post        | 1           | comment 2   | +-------------+-------------+-------------+-------------+ | 3           | event       | 1           | comment 3   | +-------------+-------------+-------------+-------------+ 

2. multiple comments tables

page (post, event) has many page comments, foreign key page_id

 page_comments table +-------------+-------------+-------------+ | id          | page_id     | text        | =========================================== | 1           | 1           | comment 1   | +-------------+-------------+-------------+  post_comments table +-------------+-------------+-------------+ | id          | post_id     | text        | =========================================== | 1           | 1           | comment 2   | +-------------+-------------+-------------+  event_comments table +-------------+-------------+-------------+ | id          | event_id    | text        | =========================================== | 1           | 1           | comment 3   | +-------------+-------------+-------------+ 

i have used specific example, can apply other 1:n tables or m:n (tags), simple showcase, should good.

we should discuss

• performance concerns
• design pros , cons

initial thoughts

• case 1 means less tables in db, easier read, reusable application code
• case 1 better when doing query on comments (would have use union @ case 2)
• case 2 better in regards of normalization (3nf)
• case 2 easier backup (dump) parts of system, e.g. pages comments
• case 2 should better performance because less rows => faster