Database: Multiple tables or just one table?

Question

For example I have photos and videos tables, I can comment on these, but when I send it to database which way is better?

1. To have 2 tables for comments: photo_comments and video_comments

2. Or to have 1 table comments and create a row inside the table like type and put there if it's a photo_comment or video_comment

I think the 1 is faster because I have less data when I need to query the table but maybe the 2 is easier to use.

Please let me know what's the best way, speed is very important for me.

I'm talking about a very big system with millions of data, millions of comments, so I want the fastest way to get the results, for me doesn't matter if I need to code more or need to keep in mind something in plus, results are much more important!

Answer 1

If you really have two separate data tables photos and videos, I would always choose to use two separate comments tables, too.

Why?

If you put all your comments into a single comments table, but that references media from two separate data tables, there's no way you can easily set up a referential integrity between your comments table and the two data tables. There are some workarounds (like having two separate reference fields, one for each), but none are really very compelling. Not having a referential integrity will ultimately lead to "zombie" data that doesn't belong to any existing media entry.

Having two comments tables allows each comment table to properly reference its associated data table, thus your data integrity in the database will be better.

For that reason, if you have two separate data tables, I would always choose to use two separate comments tables as well.