如何在MySQL中使用继承? [英] How would inheritance be used in MySQL?
问题描述
因此，我正在阅读一本有关数据库设计原理的书，并进入了有关继承的章节，但是我对如何将子类与其在MySQL中的超类连接"感到困惑?
So I'm reading a book on database design principles and came on the chapter about inheritance, but I'm confused on how I could "connect" sub classes with their super class in MySQL ?
例如，表结构如下所示:
The table structure would, for example, look like this
那么我该如何将这两个子类与它们的超类联系起来，以便我可以轻松地执行类似于嘿，请给我分配给他的人员正确的合同类型"这样的查询.
So how would I relate these two sub classes with their super class so that I could easily do a query similar to " hey, get me a correct contract type for a Person he is assigned to ".
一个人可以有一个，甚至两个都有，例如，如果一个人是兼职兼做讲师的学生，以资助学生的学业，或者是一名讲师，从事某种额外的活动课程(因此他既是学生又是讲师).
A person could have one of each, or even both of them, if, for example, a Person is a student doing a part time job as a lecturer as well to finance it's schoolarship, or a Lecturer on some kind of extra course ( so he is a student as well as a lecturer ).
推荐答案
有多种方法可以解决此问题.像Hibernate这样的OR-映射器都实现了它们.但是您也可以手动执行此操作:
There are various approaches to solve this problem; OR-Mappers like Hibernate implement them all. But you can do this manually, too:
-
最简单的方法称为单表继承".这包括使用区分符列，该列允许您存储类型名称或其他类型标识符，以便能够区分类型.这是最简单的工作方式，但是请注意，需要权衡以下因素:
The most simple one is called "Single Table Inheritance". This includes using a discriminator column which allows you to store the type name or another type identifier in order to be able to seperate types. This is the easiest way to work with, but be aware, there are these tradeoffs:
- 您不能在仅以一种叶子类型出现的列中使用NOT NULL约束
- 具有许多列的宽表在使用新类型时变得更加广泛，这将降低整体性能
第二个称为联接继承"，它反映了您的UML模型.您将有一个表"Contract"，一个表"student_contract"和一个表"lecturer_contract"，每个表仅保留与类型相关的数据，而不是其父类型.您将使用SQL JOIN选择一种特定类型的数据.这种方法使您可以拥有较小的表(快速)，但是随着更多的继承(更多的JOIN)而变得越来越复杂.核心权衡因素是:
The second one is called "Join Inheritance" and it reflects your UML model. You will have a table "Contract", a table "student_contract" and a table "lecturer_contract", every table only keeps the data related to the type, not of it's supertype. You will use a SQL JOIN to select the data of one specific type. This approach enables you to have small tables (fast) but growing complexity with more inheritance (more JOINs). The core tradedoff is :
- 数据分布良好
- 真正复杂的继承结构上的可怕sql(无论如何都是不好的样式！)
最后一种方法是每个实体的表"，这意味着您将为每种叶子类型创建一个表-在您的情况下为"Student_Contract"和"Lecturer_Contract".这是一种不错的方法，但是请注意，这也是一个折衷；-)您必须在所有表中安全生成键值(例如，使用SEQUENCE).并非每个数据库都支持此构造(例如，mysql不支持SEQUENCES作为可在多个表中重复使用的构造).
The last approach is "Table per Entity" and this means you will create a table for each leaf type - in your case "Student_Contract" and "Lecturer_Contract". This is a nice approach, but beware, there also is a tradeoff ;-) You have to generate you key values safe along all tables (e.g. by using a SEQUENCE). This construct is not supported by every database (e.g. mysql does not support SEQUENCES as a construct reusable along multiple tables).
出于学术目的，我建议您尝试1)为了简单起见
For academic purposes, I'd suggest you to try 1) for the sake of simplicity
对于真实的项目，请明智地选择-我基于2)进行的大多数项目
For real projects, choose wisely - most projects I made based on 2)
您的示例最终将取决于人与合同之间的关系，如下所示:
You example would end up- depending on the relationship between Person and Contract - in something like this:
PERSON (1) ----- (N) PERSON_CONTRACT (N) ---- (1) CONTRACT
(M与N的关系，如果可以将一个人多重分配给合同)
(M to N relationship, if the person can be multiply assigned to a contract)
或
PERSON (1) --------- (N) CONTRACT
(1:N人与一个合同之间的关系.一个合同只能有一个人，一个人可以有多个合同)
(1:N relationship between person an contract. A contract can only have one person, a person can have multiple contracts)
这篇关于如何在MySQL中使用继承?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
