具有相同列的两个表或具有附加列的一个表? [英] Two tables with same columns or one table with additional column?
问题描述
说我有两个表(苹果和橘子)具有相同的列,只是一个不同的表名。将这转换为一个表(让我们说它叫做Fruit)有一个额外的列'type',然后将存储一个苹果或橙的值有什么优点/缺点?
编辑以澄清:
CREATE TABLE apples
(
id int,
weight int,
varchar(255)
)
CREATE TABLE oranges
(
id int,
weight int,
品种varchar(255)
)
或
CREATE TABLE fruit
b $ b id int,
weight int,
variety varchar(255),
type ENUM('apple','orange')
)
取决于约束:
- 在
oranges
(或相反)上不存在苹果
上的检查? - 您需要在两个表中保持键的唯一性(因此,
apple
可以具有与某些橙$ c $ <>如果这两个问题的答案是:yes和 no,将表格分开(因此可以根据表格特定 1 )。
:no和yes,将它们合并在一起(因此您可以创建跨两者的键)。
如果答案是yes和yes,请考虑模拟继承 2 :
>
< hr>
1 查找数据是一个看起来很相似的表格的典型例子,但必须保持分开,这样FK可以保持分开。
sup>具体来说,这是表示继承的单独表中的所有类策略(aka。类别,子类,子类型,概括层次等)。您可能需要查看此信息了解详情。
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?
Edit to clarify:
CREATE TABLE apples ( 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') )
解决方案Depends on constraints:
- Do you have foreign keys or CHECKs on
apples
that don't exist onoranges
(or vice-versa)? - Do you need to keep keys unique across both tables (so no
apple
can have the same ID as someorange
)?
If the answers on these two questions are: "yes" and "no", keep the tables separate (so constraints can be made table-specific1).
If the answers are: "no" and "yes", merge them together (so you can crate a key that spans both).
If the answers are: "yes" and "yes", consider emulating inheritance2:
1 Lookup data is a typical example of tables that look similar, yet must be kept separate so FKs can be kept separate.
2 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.
这篇关于具有相同列的两个表或具有附加列的一个表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
- Do you have foreign keys or CHECKs on