Database: Multiple tables or just one table?_问答_开发者

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

To have 2 tables for comments: photo_comments and video_comments
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 wit开发者_JS百科h 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!

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.

It depends a bit more on how photos and videos are structured. Consider the following DB Design:

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 Photo and Video both had a FK to MediaType and to Media, I would make Comments relate to the Media table instead of either one, and not to the Photos or Videos table directly. This is often the type of design I use when Photo and Video have a lot of common properties. It's especially useful when you want to do things like security because you aren't boxed into repeating the same visibility and ownership constructs on each type of media you're dealing with. It's also quite fast to query because many queries often look only for common properties, or just type-specific rows, so some tables don't need to be included. Designing the database by modeling these IS-A relationships also keeps your indexes highly selective, which means speed.

If you're locked into your design and Videos and Photos have no commmon "base table", then I would make a separate comments table for each.

Why not having only one comment table? Is there any diffrence between a comment on a video or a photo? If not you should only have a column that holds the foreign key for the video/photo the comment is poiting to and an additional column with the type ENUM that holds the information of the type of resource the comment is ment for.

Using an ENUM will keep your queries very fast (as it is saved as a number) and makes it easy to use string in your query.

Splitting up the tables would be better performance-wise, since you wouldn't have to query on an extra "comment type" column. The downside of doing things this way is not reusing code (possibly in the future, if you add comments to other things). But it doesn't sound like you're concerned with that.

I don't think that the choice of whether to have 1 or 2 tables for comments is going to have any appreciable impact on the performance of your application.

You should choose whichever one makes more sense in the context of your application.

For example, if comments on photos and comments on videos are both going to act in the same way then you should have one table, if however (for example) comments on videos are allowed to be twice as long as comments on photos, or comments on photos have an additional "ranking" field or something, then 2 tables would make more sense.

your queries will either look like

select * from comments where linked_id = 555

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

(with comment type=1 meaning it's a video).

As long as comment type as an index, they will basically be just as fast.

Only thing I would consider, is columns. If video comments has a different set of comments from picture comments, split em up. If everything is the same, keep em together.