数据库设计：多个表与单个表 [英] Database Design: Multiple tables vs a single table
问题描述
我在做一个网站，其中有不同类型的项目，如博客，帖子，文章等。用户可以将他们中的任何一个设置为他/她的喜爱。现在，当我接近这个事情，我有两个选项
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
- 为每种类型的用户收藏夹
$ b $
< b
第一个结构的问题是，我将不得不查询很多表以显示特定用户的收藏。但它会让我很容易将喜好分组到不同的类别。
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.
如果它有帮助，我使用 MySql
If it helps, I am using MySql
推荐答案
似乎你已经知道答案了，但是记住，让你设计的系统简单的修改为业务模型随着时间的推移，或者他们最终失败（这是一个概括，但你得到的想法）。推论是，如果你做一个刚性模型，快或慢，它是刚性的，变化将更难，最终用户不会看到差异，因此没有钱/幸福的变化，除非是一个非常糟糕的变化。
你的问题不是技术在一个方式查询在引擎上工作，但更多的哲学，方便的变化与表观速度。
问自己，有一个规范化数据库的优势是什么？想想一个干净的架构和设计，性能是今天世界最少的问题，因为处理是便宜和存储也。但设计是昂贵的。
正规化是为了使系统不依赖于最后时刻的决定，而是依赖于结构化的设计过程。
大表对于MySql来说不是一个大问题，但是它们是维护，修改和扩展的一大笔交易。它不只是添加一个列，它是关于数据本身的刚性结构。最终，您将只添加包含索引的列，这些索引将指向小表。 MySql将会绕过所有的数据。
所以我会去第一个，很多小表，多对多。
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.
这篇关于数据库设计：多个表与单个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
