具有子类型交叉链接的超类型/子类型数据库设计
对于经验丰富的数据库开发人员来说，这可能是一个简单的问题，但我正在努力……我在将某个 ER 图转换为数据库模型时遇到了麻烦，任何帮助都是值得赞赏的。
我的设置类似于本演示文稿的幻灯片 17： http://www.cbe.wwu.edu/misclasses/mis421s04/presentations /supersubtype.ppt
幻灯片 17 显示了一个 ER 图，其中 Employee 超类型具有 Employee Type 属性，而 Employee 类型本身作为子类型（时薪、受薪和顾问），这和我的设计情况非常相似。
就我而言，假设受薪员工是唯一可以成为其他员工老板的人，我想以某种方式表明某个受薪员工是否是小时工和/或受薪员工和/或顾问的老板（要么没有，要么两者兼而有之） ），考虑到这些是一对多关系，如何在数据库模型中进行设计？
我可以在它们之间建立 PK-FK 关系，这将导致所有表都有两个 FKey 并且（例如顾问具有 FK_Employee 和 FK_SalariedEmployee）和 SalariedEmployee 引用自身，但我一直认为这可能不是最明智的解决方案......尽管我不确定为什么（诚信问题？）。
这是一种可以接受的解决方案还是有更好的解决方案？
预先感谢您的任何帮助！
This is probably a simple problem for an experienced database developer, but I'm struggling... I have trouble translating a certain ER diagram to a DB model, any help is appreciated.
I have a setup similar to slide 17 of this presentation:
http://www.cbe.wwu.edu/misclasses/mis421s04/presentations/supersubtype.ppt
Slide 17 shows an ER diagram with an Employee supertype having an Employee Type attribute and as subtypes the Employee Types themselves (Hourly, Salaried and Consultant), which is very similar to my design situation.
In my case, suppose Salaried Employees are the only ones that can be bosses of other employees and I wanted to somehow indicate if a certain Salaried employee is the boss of the Hourly and/or Salaried Employee and/or Consultant (either, none or both), how could that be designed in a database model, also considering these are one-to-many relationships?
I can put a PK-FK relationship between them, which would result in all tables having two FKeys and (like Consultant having FK_Employee and FK_SalariedEmployee) and SalariedEmployee referencing itself, but I keep thinking that might not be the wisest solution....although I'm not sure why (integrity issues?).
Is this or an acceptable solution or is there a better one?
Thanks in advance for any help!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您的案例看起来像是被称为“泛化专业化”（简称 Gen-Spec）的设计模式的一个实例。 gen-spec 模式对于面向对象的程序员来说很熟悉。在教授继承和子类时，教程中对此进行了介绍。
实现 gen-spec 模式的 SQL 表的设计可能有点棘手。数据库设计教程经常掩盖这个主题。但在实践中却一再出现。
如果您在网上搜索“泛化、专业化关系建模”，您会发现几篇有用的文章，教您如何做到这一点。您还会多次被指出这个主题以前在本论坛中出现过。
这些文章通常向您展示如何设计一个表来捕获所有通用数据，并为每个子类设计一个专用表，该表将包含特定于该子类的所有数据。有趣的部分涉及子类表的主键。您不会使用 DBMS 的自动编号功能来填充子类主键。相反，您将对应用程序进行编程，以便将从通用表获得的主键值传播到相应的子类表。
这在通用数据和专用数据之间创建了双向关联。每个专用子类的简单视图将一起收集通用和专用数据。一旦掌握了它的窍门，这很容易，而且性能相当好。
在您的具体情况下，声明 FK 的“老板”来引用受薪员工表中的 PK 就足以达到目的。这将产生您想要的双向关联，并且还可以防止无薪员工被称为老板。
Your case looks like an instance of the design pattern known as “Generalization Specialization” (Gen-Spec for short). The gen-spec pattern is familiar to object oriented programmers. It’s covered in tutorials when teaching about inheritance and subclasses.
The design of SQL tables that implement the gen-spec pattern can be a little tricky. Database design tutorials often gloss over this topic. But it comes up again and again in practice.
If you search the web on “generalization specialization relational modeling” you’ll find several useful articles that teach you how to do this. You’ll also be pointed to several times this topic has come up before in this forum.
The articles generally show you how to design a single table to capture all the generalized data and one specialized table for each subclass that will contain all the data specific to that subclass. The interesting part involves the primary key for the subclass tables. You won’t use the autonumber feature of the DBMS to populate the sub class primary key. Instead, you’ll program the application to propagate the primary key value obtained for the generalized table to the appropriate subclass table.
This creates a two way association between the generalized data and the specialized data. A simple view for each specialized subclass will collect generalized and specialized data together. It’s easy once you get the hang of it, and it performs fairly well.
In your specific case, declaring the "boss of" FK to reference the PK in the Salaried Employees table will be enough to do the trick. This will produce the two way association you want, and also prevent employees who are not salaried from being referenced as bosses.