合并MySQL数据表是最佳实践吗? [英] Is it best practice to combine MySQL data tables?

问题描述

我正在考虑更改数据库方案以减少表的数量.我有几个包含不同但又相似的数据的表，并且想知道是否最好的方法是将其保留为这种方式，或者合并它们是否会带来麻烦.

I am considering changing a database scheme to reduce the number of tables. I have several tables that contain different-yet-similar data, and am wondering if it is best practice to leave it this way, or if there would be complications with combining them.

例如，假设我有以下两个表:

For example, let's say I have the following two tables:

Table `status`

`status_id` | `status_text`
---------------------------
1           | Open
2           | Closed
3           | On Hold

Table `type`

`type_id`   | `type_text`
---------------------------
1           | Regular Work
2           | Advanced Work
3           | Warranty Work

将这些内容合并到一个表格中(例如以下表格)是否有利?

Would it be advantageous to combine these into a table, such as the following?

Table `text`

`id`        | `type`        | `text`
-------------------------------------------
1           | 1             | Open
2           | 1             | Closed
3           | 1             | On Hold
1           | 2             | Regular Work
2           | 2             | Advanced Work
3           | 2             | Warranty Work

type 列将与代表数据集类型的PHP常量相关.

The type column would correlate to PHP constants representing the data set type.

我目前大概有6个表，其中包含精确的数据表.令我感到困惑的是它们是如此相似，并且每个仅容纳2-5行.我真的很想像上面一样将它们结合起来，但是不确定是否会因此带来麻烦，或者是否违反了最佳实践.

I currently have probably 6 tables with data in this exact scheme. It just bugs me that they are so similar, and each only holds 2-5 rows. I really want to combine them as I have above, but am not sure if there would be complications down the road from it, or if it is breaking best-practices.

我确实意识到 id 列会发生冲突，并且不会成为主键的候选者，但是它将与 type 一起用作唯一键，以防止发生冲突.我不担心auto_increment，因为这些表是手动管理的.

I do realize the id column would conflict and would not be candidate for a primary key, but it would be a unique key along with type to prevent collisions. I am not worried about auto_increment, as these tables are managed manually.

还要记住的另一件事是，这些表涉及多个JOINS.除了在ON子句中添加一个条件之外，我看不到它使JOIN变得更加复杂.

Also another thing to keep in mind is that these tables are involved in several JOINS. I don't see it complicating the JOINs much more other than adding one more condition to the ON clause.

如果这是一个重复的问题，我深表歉意，因为我不是提出有关选择数据的常见问题，而是方案，这似乎是一个很难回答的问题.

I apologize if this is a duplicate question, it seemed a hard subject to look up, as I am not posing a common question about selecting the data, but rather the scheme.

推荐答案

有优缺点，而且通常归结为个人喜好.我个人更喜欢多个不同的查找表(因为每个表都对应于我域内的一个不同的实体定义，尽管它们由巧合地相似的原始类型组成).出于您陈述的原因，其他人有时更喜欢单个超级查找"表.

There are pros and cons, and it often comes down to personal preference. I personally prefer multiple disparate lookup tables (since each one corresponds to a different entity definition within my domain, despite being composed of coincidentally similar primitive types). Others sometimes prefer the single "super lookup" table for the reasons you state.

我经常看到的一种方法是拥有这样的东西:

One approach I've often seen is to have something like this:

LookUp
----------
ID
LookUpTypeID
Value
DisplayText
etc.

LookUpType
----------
ID
Name

这提供了一种相当直接的方法来将您的查找值组织到单个结构中.而且，如果您发现自己想要分离出类型以模仿"不同的表(例如，如果像我这样的开发人员加入您的团队并引起了很大的反响)，则您始终可以根据类型和使用来创建视图那些在查询中.

This provides a fairly straightforward way to organize your lookup values into the single structure. And if you ever find yourself wanting to separate out the types to "mimic" the disparate tables (for example, if a developer like me joins your team and makes a lot of noise), you can always create views based on the types and use those in queries.

多个小表本质上没有错.如果它们真正包含的数据意味着有所不同，那么我认为应该将其分开.域中实体的概念含义比构成它们的数据类型更重要.

There's nothing inherently wrong with multiple small tables. If the data they contain genuinely means something different then I would argue that it should be separated. The conceptual meaning of the entities in the domain is far more important than the data types of which they're composed.

多个小表对数据库引擎影响不大.它已经过优化，您不会通过组合表格来帮忙.实际上，随着时间的流逝，您的查询可能会变得更加晦涩难懂.从数据库中查询数据的含义开始被表中的实现细节所笼罩.您可以通过添加视图来缓解，以再次逻辑上分离数据，但是如果您发现需要这样做，那为什么要首先将其组合起来?

And multiple small tables makes little difference to the database engine. It's pretty optimized, you're not doing it any favors by combining the tables. In fact, you may find that your queries become slightly more obtuse over time as you do this. The meaning of the data being queried from the database starts to get clouded by implementation details within the tables. You can mitigate this by adding views to logically separate the data again, but if you find yourself needing to do that then why combine it in the first place?

这篇关于合并MySQL数据表是最佳实践吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！