两个具有相同列的表或一个具有附加列的表？

Say I have two tables (Apples and Oranges) with the same columns and just a different table name. Would there be any advantages/disadvantages to turning this into one table (lets say its called Fruit) with an additional column 'type' which would then either store a value of Apple or Orange?

假设我有两个表（苹果和橘子），它们具有相同的列，只有一个不同的表名。是否有任何优点/缺点将其转换为一个表（简称其称为Fruit），并附加一个“类型”列，然后存储Apple或Orange的值？

Edit to clarify:

编辑澄清：

CREATE TABLE apples ( id int, weight int, variety varchar(255) )

CREATE TABLE apples（id int，weight int，variety varchar（255））

CREATE TABLE oranges ( id int, weight int, variety varchar(255) )

CREATE TABLE oranges（id int，weight int，variety varchar（255））

OR

要么

CREATE TABLE fruit ( id int, weight int, variety varchar(255), type ENUM('apple', 'orange') )

CREATE TABLE fruit（id int，weight int，varchar（255），type ENUM（'apple'，'orange'））

3 个解决方案

#1

Depends on constraints:

取决于约束：

• Do you have foreign keys or CHECKs on apples that don't exist on oranges (or vice-versa)?
• 您是否在橘子上不存在的苹果上有外键或CHECK（反之亦然）？
• Do you need to keep keys unique across both tables (so no apple can have the same ID as some orange)?
• 您是否需要在两个表中保持唯一键（因此没有苹果可以与某些橙色具有相同的ID）？

If the answers on these two questions are: "yes" and "no", keep the tables separate (so constraints can be made table-specific¹).

如果这两个问题的答案是：“是”和“否”，则保持表格分开（因此约束可以特定于表格1）。

If the answers are: "no" and "yes", merge them together (so you can crate a key that spans both).

如果答案是：“no”和“yes”，则将它们合并在一起（这样你就可以创建一个跨越两者的键）。

If the answers are: "yes" and "yes", consider emulating inheritance²:

如果答案是：“是”和“是”，请考虑模拟继承2：

---

¹Lookup data is a typical example of tables that look similar, yet must be kept separate so FKs can be kept separate.

1查找数据是看起来相似的表的典型示例，但必须保持独立，因此FK可以保持独立。

² Specifically, this is the "all classes in separate tables" strategy for representing inheritance (aka. category, subclassing, subtyping, generalization hierarchy etc.). You might want to take a look at this post for more info.

2具体来说，这是表示继承的“所有类在单独的表中”策略（也就是类别，子类，子类型，泛化层次等）。您可能需要查看此帖子以获取更多信息。

#2

If there really is not any further business rules (and resultant underlying data requirements) that separate the two sub-types then I would use one table with an fk to a FruitType lookup table.

如果确实没有任何进一步的业务规则（以及由此产生的基础数据要求）将两个子类型分开，那么我将使用一个带有fk的表到FruitType查找表。

You dont mention what you will be using to access the schema which may affect which approach you take (e.g. if you are using a platform which provides an ORM to your database then this may be worth noting).

您没有提到您将使用什么来访问可能影响您采用哪种方法的架构（例如，如果您使用的是为您的数据库提供ORM的平台，那么这可能值得注意）。

#3

The advantage would be normalization. Your tables would then be in 2NF (second normal form). Your fruit type would be a foreign key to a table with those fruits like so:

优点是规范化。您的表格将为2NF（第二范式）。你的水果类型将是一个表的外键，其中包含以下水果：

CREATE TABLE fruit_type (type varchar(15))

CREATE TABLE fruits (id int, weight int, variety varchar(255), type varchar(15))