用户活动（ALA脸谱）数据库的方法。大JOIN或多个简单的查询？ [英] User activity (ala facebook) DB approach. Big JOIN or multiple simple queries?

问题描述

我需要显示用户的最后一个活动的网站。 已发布的评论，图像/视频上传，投票的文章等等...

I need to display the users last activity in the website. Reviews posted, images/video uploaded, voted articles and so on...

我有两个表：

user_activity 表用户ID，日期时间，activityID和外部ID列。

'user_activity' table with 'userID', 'datetime', 'activityID' and 'externalID' columns.

活动'表'activityID和活动。

'activities' table with 'activityID' and 'activity'.

让我们说'activityID'= 4，那么我就知道它冷藏到上传的视频（通过看活动表），那么我也知道，外部ID冷藏集装箱从第三个表的VIDEOID'叫'影片。

Lets say that 'activityID' = 4, then I know that it reefers to an uploaded video (by looking at the 'activities' table) then I also know that externalID reefers to the 'videoID' from a third table called 'videos'.

同为每种类型的活动，如果它的审查，然后外部ID冷藏在'评论'表'reviewID。

The same for each type of activity, if its a review then 'externalID' reefers to 'reviewID' in the 'reviews' table.

在显示用户的活动，我不只想说用户上传的图像，但也显示上传的图像或视频或什么的。

When showing the user activity I don't want only to say the user uploaded an image but also show the uploaded image or video or whatever.

因此​​，

在查询用户最后一次活动，我可以通过两种方式获取数据：

When querying for the user last activity I can get the data by two ways:

1）获得从'user_activity'表中的数据，然后，进行到外部表另一个查询每行（评论，视频，图像...）根据'activityID'

1) Get the data from 'user_activity' table and then, make another query for each row to the external table (reviews, videos, images...) depending on the 'activityID'.

2）造成很大的LEFT OUTER JOIN的所有表（user_activity，评论，视频，图像...的）在一起，然后仅使用相关列基于activityID。

2) Make a big LEFT OUTER JOIN of all the tables (user_activity, reviews, videos, images...) together and then use only the relevant column based on 'activityID'.

于是方式1将导致数据库查询了很多，因为我需要一个不同的查询每一项活动的行。而方式2将导致大JOIN。

So way 1 will cause a LOT of DB queries because I need a different query for each activity row. And way 2 will result in a BIG JOIN.

这是优点缺点和方法的任何好的建议是更好??

Any good advice on PROS CONS and with method is better??

推荐答案

你有什么设置被称为多态关联。这是一个已经以不同的方式解决了一遍又一遍的问题。你可以现在你知道找了很多资料了关于这个的interwebz如何找到它。

What you have setup is called a polymorphic association. This is a problem that has been solved over and over again in different ways. You can find a lot of information out on the interwebz about this now that you know how to find it.

要更具体回答你的问题：这要看情况。 join可以是数据库更容易，如果你设置表使用InnoDB和你只能加入外键。不幸的是，InnoDB的不支持多态外键。

To answer your question more specifically: it depends. JOINs can be easier on the database if you setup the tables to use InnoDB and you only join on foreign keys. Unfortunately, InnoDB doesn't support polymorphic foreign keys.

一般来说，它很难扩展数据库，而不是扩展您的应用程序，所以什么可以做，从数据库层到应用层的移动工作，是一件好事。然而，这真的只有真正的你担心缩放（记住，premature优化可能是一个致命的打击，以启动）。

Generally speaking, it's harder to scale the database than to scale your application, so anything you can do to move work from the DB layer into the application layer is a good thing. However, that's really only true of you're worried about scaling (and remember, premature optimization can be a death-blow to a startup).

检查出前这个问题的答案在Quora的一些更详细的信息：<一href="http://www.quora.com/What-are-the-scaling-issues-to-keep-in-mind-while-developing-a-social-network-feed?q=scaling+social+network"相对=nofollow>什么是比例问题，要牢记在开发一个社交网络供稿？

Check out the top answers to this question on Quora for some more detailed information: What are the scaling issues to keep in mind while developing a social network feed?

这篇关于用户活动（ALA脸谱）数据库的方法。大JOIN或多个简单的查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！