ansaurus

Question

Database: Multiple tables or just one table?

Answer 1

A:

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.

Jon Smock 2010-07-11 13:45:03

Hmm nice answer however I think I can add comments to other things in future because I can create a new table it's not so hard :P, but my question is general I will make lots of things in this way :)

CIRK 2010-07-11 13:48:01

Answer 2

A:

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.

Kau-Boy 2010-07-11 13:50:15

The reason I would take one table: When you extend your comment system our make changes to the columns, you only have to change one table. Even if this one table gets very large, it will be very fast in getting the results for a video/photo, if you use indexes correctly. I have tables with more that 20 million entries and searching on them is still very fast (under 100ms even for very complex searches) so in this case the size shouldn't be a problem. And you can always partition your table.

Kau-Boy 2010-07-11 13:56:09

U say something :P

CIRK 2010-07-11 14:00:02

I get this error when I want to create a table with a `type` row with `ENUM` `#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL) ENGINE = MyISAM' at line 1 `

CIRK 2010-07-11 14:20:23

Hmm I think I was leaving the Length/Values Field empty.

CIRK 2010-07-11 14:27:07

Your ENUM suggestion is not a good recommendation for performance. The suggestion to use one table with this existing table structure isn't a good one for maintaining and enforcing referential integrity, either.

Dave Markle 2010-07-11 14:41:12

Using a INT might be a little faster but then you have to document which number stands for which type. If the implementation works correct, the RI shouldn't be a problem. And how about maintaining 10 comment tables compared to only one table? Where is the improvement two only have one table if all the comments have the same structure? Why would systems like blogging software then use only one table if it is such a bad idea?

Kau-Boy 2010-07-11 15:51:07

Guys 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!

CIRK 2010-07-11 16:21:24

The best way to get fast results is to cache your queries using something like memcached (http://memcached.org/). To optimize your queries you have to try out your queries on big data sets. Noone can relly tell you which aproach will be the fastest whitout knowing your whole database setup and the setting of your database and the indexes and joins you use.

Kau-Boy 2010-07-11 16:29:50

@Kau: You can't create a foreign key constraint from two tables to one table. There's nothing wrong with using one table, but if you're going to do that, you have to normalize the database properly using a supertype/subtype pattern (see my answer). Saying that RI "shouldn't be a problem" as a defense for a poor design is a red flag.

Dave Markle 2010-07-11 16:56:19

But overly normalizing a database is also not good!

Kau-Boy 2010-07-11 17:14:25

That's true, but you are advocating a "worst practice" here. -1.

Dave Markle 2010-07-11 20:39:07

Worst pracite would it be not normalizing any table (like keeping videos and photos in one table). But I see, you needed to find a vague reason for your down vote. And hey, my answer has been accepted so I am not that wrong! There is not way to do it the best in all scenarios and I was just pointing out how it might be done and keeping it maintainable.

Kau-Boy 2010-07-11 21:15:27

Answer 3

A:

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.

Kragen 2010-07-11 13:51:25

On every type of content the comments will be the same but I asked this question because I want to make sure which one is faster, I will have lots of comments speed matters.

CIRK 2010-07-11 13:54:44

Answer 4

A:

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.

Dave Markle 2010-07-11 13:53:04

Interesting way, I've never seen it before :)

CIRK 2010-07-11 14:02:45

I also like to make sure that MediaTypeID is has a very small sized PK. I often use a TINYINT or CHAR(1) to keep the index sizes compact. Note that the MediaID, MediaTypeID should be created as a *composite* foreign key.

Dave Markle 2010-07-11 14:06:30

What do you need the Media table for. It is absolutely useless and needs another join just to get the name of a media.

Kau-Boy 2010-07-11 15:56:43

Media is used for common properties. Since I don't know his database design, I just left Name in there. This is the classic, accepted way of implementing an IS-A relationship with relational databases. Oftentimes, you begin to notice that most of the properties of your Photo and Video tables are in fact common. The parent table (such as Media) often becomes the "Main" table you query off of in most scenarios.

Dave Markle 2010-07-11 16:44:42

Answer 5

A:

your queries 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 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.

bwawok 2010-07-11 13:56:44

Answer 6

+1 A:

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.

marc_s 2010-07-11 14:21:45

I will not have just `photos` and `videos`, I will have others things too, so for making for everything a `comment`, hmm I don't know, and what about commenting on comments? I should create tables for those too?

CIRK 2010-07-11 16:28:31

ansaurus

tags:

views:

answers:

Database: Multiple tables or just one table?

related questions