Database: multiple tables or only one table?

Question

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

• To have 2 tables for comments: photo_comments and video_comments

• Or have 1 comments table and create a row inside the table, for example, type and put there if it is photo_comment or video_comment

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

Please let me know that the best way, speed is very important to me.

I’m talking about a very large system with millions of data, millions of comments, so I need the fastest way to get results, it doesn’t matter for me whether I need more code or keep in mind something positive, the results are much more important!

Answer 1

Why not have only one comment table? Is there a difference between a comment on a video or photo? If not, you should have only a column that stores the foreign key for the video / photo to which the comment is sent, and an additional column with the ENUM type, which contains information about the type of resource for which the comment contains.

Using ENUM will support your queries very quickly (since it is stored as a number) and simplifies the use of strings in your query.

Answer 2

If you really have two separate photos and videos data tables, I would always like to use two separate comment tables.

Why?

If you put all your comments in one comments table, but referring to media from two separate data tables, you cannot easily set up referential integrity between your comment table and two data tables. There are some workarounds (for example, the presence of two separate link fields, one for each), but none of them are very convincing. The lack of referential integrity will ultimately lead to “zombie” data that does not belong to any existing multimedia record.

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

For this reason, if you have two separate data tables, I would always decide to use two separate comment tables.

Answer 3

It depends on how photos and videos are structured. Consider the following database project:

 MediaType ---------- ID * Name Media ---------- ID * TypeID OwnerName Name Size Path Photo ---------- MediaID * MediaTypeID (constraint, always set to the photo type) Height Width Video --------- MediaID * MediaTypeID (constraint, always set to the video type) Rating 

If the photos and videos had FK on MediaType and on Media, I would make comments related to the Media table, and not to one, and not to the photo table or video directly. This is often the type of design that I use when Photo and Video have many common properties. This is especially useful when you want to do something like security, because you do not insert into yourself a repetition of the same constructions of visibility and ownership on each type of media with which you deal. It also queries quite quickly, because many queries often look only at common properties or just at type strings, so some tables do not need to be included. Designing a database by modeling these IS-A relationships also makes your indexes top-secret, which means speed.

If you are locked in your design, and videos and photos do not have a commmon “base table”, then I would create a separate comment table for each.

Answer 4

Partitioning the tables would be better, since you would not have to query for an additional comment type column. The disadvantage of such actions is not to reuse the code (possibly in the future, if you add comments to other things). But that doesn't sound like you're worried about it.

Answer 5

I do not think that choosing whether there are 1 or 2 tables for comments will have a noticeable effect on the performance of your application.

You have to choose what makes sense in the context of your application.

For example, if comments on photos and comments on videos act the same way, then you should have one table, if (for example) comments on videos can be twice as long as comments on photos, or comments on photos have an additional “ranking” field or something like that, then 2 tables will make more sense.

Answer 6

your requests will either look like

 select * from comments where linked_id = 555 

or

 select * from comments where linked_id = 555 and comment_type = 1 

(with comment type = 1, which means video).

As long as the type of comments is like an index, they will be basically as fast.

The only thing I would like to consider is the columns. If the video comments have a different set of comments from the comments on the images, separate them. If everything is the same, hold them together.