数据库设计：多个表与单个表

I am making a website where there are different types of items such as blogs, posts, articles and so on. A user can set any one of them as his/her favorite. Now when I approach this thing, I have two options

我正在建立一个网站，其中有不同类型的项目，如博客，帖子，文章等。用户可以将他们中的任何一个设置为他/她的最爱。现在当我接近这个东西时，我有两个选择

Make a table for user favorites for each type of object.

为每种类型的对象创建一个用户收藏夹表。

Make a common table for all type of objects for all the users.

为所有用户创建所有类型对象的公用表。

The problem with the 1st structure is that I will have to query a lot of tables for displaying the favorites of a particular user. But it will allow me to easily group the favorites into different categories.

第一个结构的问题是我将不得不查询很多表来显示特定用户的收藏夹。但它可以让我轻松地将收藏夹分为不同的类别。

However if I have to show all the favorites on one single page and merge them all, sorted according to time, then that becomes difficult. But if I use the second model, I can easily get the latest favorites, and also grouping them according to object type is not difficult, but I will have one large table site wide.

但是，如果我必须在一个页面上显示所有收藏夹并将它们全部合并，根据时间排序，则变得困难。但是如果我使用第二个模型，我可以很容易地获得最新的收藏夹，并且根据对象类型对它们进行分组并不困难，但是我会有一个大的表站点。

Which of the two strategies will be more scalable.

这两种策略中哪一种更具可扩展性。

The 1st one entails multiple database queries, and the second one entails a large single table.

第一个需要多个数据库查询，第二个需要一个大的单个表。

If it helps, I am using MySql

如果有帮助，我正在使用MySql

2 个解决方案

#1

It seems that you already know the answer, but remember, keep the systems you design simple to modify as business models always change over time or they eventually fail (it's a generalization but you get the idea). A corollary of that is if you make a rigid model, fast or slow, it's rigid, changes will be harder and the end user won't see the difference, hence no money/happiness change is achieved, unless it's a very bad change. Your problem is not technical in a way a query works on the engine but more of a philosophical one, easy changes versus apparent speed. Ask yourself, what's the advantage of having a normalized database? Think about a clean architecture and design, performance is the least problem in todays world as processing is cheaper and storage also. But design is expensive. Normalization was made to make systems that don't depend on last moment decisions but on a structured design process. Big tables are not a big deal for MySql but they are a big deal to maintain, modify and expand. It's not just adding one more column, it's about the rigid structure of the data itself. Eventually in time you will just add columns that contain indexes, and those indexes will be pointing to small tables. MySql will be plowing it's way around all that data anyway. So i'll go for the first one, a lot of small tables, many-to-many.

您似乎已经知道了答案，但请记住，保持您设计的系统易于修改，因为商业模式总是会随着时间的推移而变化，或者最终会失败（这是一种概括，但您明白了）。其中的一个必然结果是，如果你制作一个严格的模型，无论是快速还是慢速，它都是僵化的，变化会更加困难，最终用户也不会看到差异，因此不会实现金钱/幸福的变化，除非这是一个非常糟糕的变化。你的问题不是技术问题，而是查询在引擎上运行的方式，而不是一个哲学问题，容易改变而不是表观速度。问问自己，拥有标准化数据库的优势是什么？想想干净的架构和设计，性能是当今世界中最不重要的问题，因为处理更便宜，存储也更便宜。但设计很昂贵。规范化是为了使系统不依赖于最后时刻的决定，而是依赖于结构化设计过程。大表对MySql来说不是什么大问题，但它们对于维护，修改和扩展来说是一个大问题。它不只是添加一个列，而是关于数据本身的刚性结构。最终，您将只添加包含索引的列，这些索引将指向小表。无论如何，MySql将抄袭所有数据。所以我会选择第一个，很多小桌子，多对多。

#2

I have this design on my website. My modules are: news, articles, videos, photos, downloads, reviews, quizzes, polls, etc etc. All in separate tables. I have a likes table where users can like or dislike a post (in your case favorites). The query to get these isn't that complicated.

我的网站上有这个设计。我的模块是：新闻，文章，视频，照片，下载，评论，测验，民意调查等等。所有这些都在单独的表格中。我有一个喜欢的桌子，用户可以喜欢或不喜欢一个帖子（在你喜欢的情况下）。获取这些的查询并不复杂。

First off for the most part MOST of my tables for the modules are structured the same way:

首先，大部分模块的表格大部分是以相同的方式构建的：

id
ID
title
标题
content
内容
user_id (author)
user_id（作者）
date
日期
etc
等等

with a few exceptions being that sometimes title is called question or there is no content column. That does not cause any issues.

除了少数例外，有时标题被称为问题或没有内容列。这不会导致任何问题。

My likes tables is set up like this:

我喜欢的表格设置如下：

id
ID
page_id
PAGE_ID
module_id (what table did it come from...I have a modules table where each module has a title, associated id, directory, etc)
module_id（它来自哪个表...我有一个模块表，每个模块都有一个标题，关联的id，目录等）
post_id (corresponds to the module table id)
post_id（对应于模块表id）
user_id (user who did the liking or posting)
user_id（喜欢或发帖的用户）
status (0 = like, 1 = dislike)
状态（0 =喜欢，1 =不喜欢）
date (when the liking/disliking took place)
日期（喜欢/不喜欢的时候）

Modules table example:

模块表示例：

id
ID
title
标题
directory
目录
post_type
post_type

Example

例

id      title              directory         post_type
 1       News                news               news
 2     Episode Guide       episodes            episode
 3       Albums           discography/albums    album

Essentially yours would have a similar set up, modifying the table structure as necessary for your needs.

基本上你的设置会有类似的设置，根据需要修改表格结构。

Query to get all the likes or favorites for a particular user:

查询以获取特定用户的所有喜欢或收藏：

$getlikes = mysql_query("SELECT DISTINCT post_id, module_id, page_id FROM likes WHERE user_id = $profile_id ORDER BY id DESC LIMIT $offset, $likes_limit", $conn);
$likes = mysql_num_rows($getlikes);

if($likes == "0"){
echo "<br><Center>$profile_username does not have any liked posts at this time.</center><BR>";
}
else {
echo "<table width='100%' cellspacing='0' cellpadding='5'>

<Tr><th>Post</th><th align='center'>Module</th><th align='center'>Page</th><tr>";

while ($rowlikes = mysql_fetch_assoc($getlikes)) {
   // echo data

$like_page_id = $rowlikes['page_id'];
$like_module_id = $rowlikes['module_id'];
$like_post_id = $rowlikes['post_id'];


// different modules have different fields for the "title", most are called title but quotes is called "content" and polls is called "questions"
if($like_module_id == "11"){
$field = "question";
}
elseif($like_module_id == "19"){
$field = "content";
}
else{
$field = "title";
}





// FUNCTIONS
PostURL($like_page_id, $like_module_id, $like_post_id);
ModTitle($like_module_id);
ModTable($like_module_id);
ModURL($like_page_id, $like_module_id);
fpgURL($like_page_id);


$getpostinfo = mysql_query("SELECT $field AS field FROM $mod_table WHERE id = $like_post_id", $conn);
$rowpostinfo = mysql_fetch_assoc($getpostinfo);
$like_post_title = $rowpostinfo['field'];

// Using my "tiny" function to shorten the title if the module is "Quotes"
if($like_module_id == "19"){
Tiny($like_post_title, "75");
$like_post_title = "\"$tiny\"";
}


if(!$like_post_title){
$like_post_title = "<i>Unknown</i>";
}
else {
$like_post_title = "<a href='$post_url'>$like_post_title</a>";
}

echo "<tr class='$altrow'>
<td>$like_post_title</td>
<td align='center'><a href='$mod_url'>$mod_title</a></td>
<td align='center'>$fpg_url</td>


</tr>";

$altrow = ($altrow == 'altrow')?'':'altrow';

} // end while

echo "<tr><Td align='center' colspan='3'>";

// FUNCTIONS - Pagination links
PaginationLinks("$cs_url/users/$profile_id", "likes");

echo "</td></tr></table>";

} // end else if no likes

Ok that may be hard for you to understand since I have alot of my own variables, but basically it gets the module id and post id from the likes table and then runs a query to get the title of the post and any other info I want like the original author.

好吧，你可能很难理解，因为我有很多自己的变量，但基本上它从likes表中获取模块ID和post id，然后运行查询以获取帖子的标题和我想要的任何其他信息像原作者一样。

I have "module" functions set up that will return the url or the title of the module given you provide an id for it.

我设置了“模块”功能，它会返回url或模块的标题，因为你为它提供了一个id。

#1

#2