用继承来设计数据库是个好主意吗？
例如，我有 2 个表：“客户”和“员工”。它们几乎相同，只有 2 个属性不同。那么我是否应该创建另一个名为“person”的表，其中包含“customer”和“staff”的所有相同属性，然后创建指向该“person”的 fk 键？就像类设计中的继承一样。
这种方法有什么缺点吗？
For example, I have 2 tables : 'customer' and 'staff'. They are almost the same, only 2 attributes are different. So should I create another table named 'person' contains all of the same attributes of 'customer' and 'staff' then create fk keys point to this 'person'? Something like inheritance in class design.
Is there any drawback to this method ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
是的，该方法有一个缺点。连接会增加查询的复杂性（在某些情况下会非常复杂），并且如果不小心的话可能会增加查询时间。
相反，执行此操作的标准方法（即，当子类之间只有少数属性不同时模拟对象继承）是执行名为 单表继承。此方法以一点未使用的数据库空间为代价来防止数据库连接。
它的工作原理如下：您创建一个表，其中包含所有属性（包括仅适用于其中之一的属性）以及用于指定对象类型的
type属性。例如，如果customer具有以下属性：id、name、email、password,order_dateAND
staff具有以下属性：id、name、email、password,hire_date然后你创建一张表，其中包含所有属性的列和类型：
id、type、name、email、password、order_date、hire_datetype列将始终包含“客户”或“员工”。如果type为“customer”，则hire_date始终为 NULL，并且毫无意义。如果type为“staff”，则order_date始终为 NULL，并且毫无意义。Yes, there is a drawback to that method. Joins increase query complexity (immensely so in some cases) and can increase query time if you're not careful.
Instead, the standard way to do this (i.e. simulate object inheritance when only a few attributes differ between the subclasses) is to do something called Single Table Inheritance. This method prevents database joins at the cost of a little bit of unused database space.
It works like this: You create one table that contains all the attributes, including the ones that only apply to one or the other, as well as a
typeattribute to specify the object type. For example, ifcustomerhas attributes:id,name,email,password,order_dateAND
staffhas attributes:id,name,email,password,hire_dateThen you create one table with columns for all the attributes and a type:
id,type,name,email,password,order_date,hire_dateThe
typecolumn will always contain either "customer" or "staff". Iftypeis "customer", thenhire_dateis always NULL, and is meaningless. Iftypeis "staff" thenorder_dateis always NULL, and is meaningless.您正在描述一个模式调用类表继承。这是一个有效的设计，但与任何其他设计一样，必须经过良好的判断才能使用它。阅读 Martin Fowler 的“企业应用程序架构模式”，了解有关其优点和缺点的更多详细信息。
有些人警告不要使用联接，但只有当您需要特定于子类的列时才需要联接。当给定查询仅需要公共列时，您可以避免额外的联接。
You're describing a pattern call Class Table Inheritance. It's a valid design, but like any other design, it must be used with good judgment. Read Martin Fowler's "Patterns of Enterprise Application Architecture" for more details on its advantages and disadvantages.
Some people caution against the use of joins, but you need a join only when you need the subclass-specific columns. When a given query only needs the common columns, you can avoid the extra join.
Pranay Rana 和 Ben Lee 都是正确的，最终的答案是：“视情况而定”。
您必须权衡子类特定列的数量与公共列的数量，以决定什么最适合您。单表继承不能很好地扩展：当您必须引入第三种类型的子类（例如供应商）时会发生什么？
就此而言，您将如何对待同时也是客户的员工？
Both Pranay Rana and Ben Lee are correct, and the ultimate answer is: "it depends".
You have to weigh up the number of sub-class specific columns against the number of common columns to decide what's right for you. Single Table inheritance doesn't scale well: what happens when you have to introduce a third type of sub-class, such as suppliers?
For that matter how are you going to treat staff that are also customers?
查找“泛化专业化关系建模”。您会发现一些关于该主题的好文章。大多数示例都遵循与 Bill 为您提供的类表继承链接相同的模式。
还有一个小细节。专用表（针对您的案例中的客户和员工）不会自动编号其 ID 字段。相反，当您填充它们时，id 字段应该获取通用表中 id 字段的副本（在您的情况下为 person）。
这使得专门的 ID 承担双重职责。它们都是对通用表中相应行的 pk 和 fk 引用。这使得连接变得更容易、更快。
创建将每个专用表与通用表连接的视图会很方便。或者，您可以创建一个大型视图，生成与您在另一个响应建议的单个表继承模式中看到的相同数据。它基本上是一堆连接的联合。
Lookup "generalization specialization relational modeling". You'll find some good articles on the subject. Most of the examples follow the same pattern as the Class Table Inheritance link that Bill gave you.
There's just one more little detail. The specialized tables (for customer and staff in your case) do not autonumber their id fieid. Instead, when you populate them, the id field should get a copy of the id field in the generalized table (person in your case).
This makes the specialized ids do double duty. They are both a pk and an fk reference to the corresponsding row in the genralized table. This makes joins easier and faster.
It can be convenient to create views that have each specialized table joined with the generalized table. Or you can make one large view that generates the same data you would see in a single table inheritance pattern suggested by another response. It's basically a union of a bunch of joins.
嗯，我说它的设计很好，因为你没有重复数据，这就是数据标准化的所在。
只有一件事是，只要您标准化，您的加入数量就会增加。
Well I say its good design because you are not repeating data and that's y the data normalization is there.
just one thing is that as much as you normalize your no of join will increase.