Let's assume we have application with pages, posts and events. With each part of this application we want to have comments. Now let's take a look into tables for our DB.
1. One comment table, object and object_id as 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, but this can apply to any other 1:N tables or even with M:N (tags), but for simple showcase, this should be good.
We should discuss
- Performance concerns
- Design pros and cons
Initial thoughts
- case 1 means less tables in DB, easier to read, reusable application code
- case 1 is better when doing query on all comments (would have to use union at case 2)
- case 2 is better in regards of normalization (3NF)
- case 2 is easier to backup (dump) parts of the system, e.g. pages itself with their comments
- case 2 should be better with performance because less rows => faster
