理解类表继承
我有一个名为 products 的产品表 它有 3 个字段（名称、型号（PK）和类名）。类对应一个表。 所以这里有一个例子：
产品表：
model | name | class_Name
z123 | Abcd | AMPS
AMPS 表：
model | attribute_1 | attribute_2
z123 | blah blah | blah blah
问题：
我是否应该有一个表来保存 PK（型号）及其相应的类名称，然后在我的产品表中使用类 ID？拥有一个包含所有模型及其类的表会更有效吗？
I have a product table called products
It has 3 fields (name,model(PK), and class_name). The class corresponds to a table.
So here is an example:
Product table:
model | name | class_Name
z123 | Abcd | AMPS
AMPS table:
model | attribute_1 | attribute_2
z123 | blah blah | blah blah
Question:
Should I have a table that holds the PK (model) and its corresponding class name, and then use the class ID in my product table? Would it be more efficient to have a table that holds all of the model's and their classes?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
已经有一个可接受的答案，但我添加以下内容是为了其他遇到此问题的人的利益。请注意，此解决方案与在主表中指示子类明显不同。在我看来，它既简单又灵活。
您的案例看起来像是被称为“泛化专业化”（简称 Gen-Spec）的设计模式的一个实例。 gen-spec 模式对于面向对象的程序员来说很熟悉。在教授继承和子类时，教程中对此进行了介绍。
实现 gen-spec 模式的 SQL 表的设计可能有点棘手。数据库设计教程经常掩盖这个主题。但在实践中却一再出现。
如果您在网上搜索“泛化、专业化关系建模”，您会发现几篇有用的文章，教您如何做到这一点。您还会多次被指出这个主题以前在本论坛中出现过。
这些文章通常向您展示如何设计一个表来捕获所有通用数据，并为每个子类设计一个专用表，该表将包含特定于该子类的所有数据。有趣的部分涉及子类表的主键。您不会使用 DBMS 的自动编号功能来填充子类主键。相反，您将对应用程序进行编程，以便将从通用表获得的主键值传播到相应的子类表。
这在通用数据和专用数据之间创建了双向关联。每个专用子类的简单视图将一起收集通用和专用数据。一旦你掌握了它的窍门，这很容易，而且性能相当好。
There's already an accepted answer, but I'm adding the following for the benefit of other people who run across this Q. Note that this solution is markedly different from indicating the subclass in the master table. And in my opinion it's both simpler and more flexible.
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.
这看起来像一个“子类”（又名“类别”）层次结构。如果您现在并且永远只有
AMPS而没有其他“子类”，那么您可以考虑将其与Product合并。否则，看起来不错。顺便说一句，在关系数据库中实现类层次结构有 3 种方法，每种方法都有优点和缺点。将所有类保存在一个表中就是其中之一，但可能很难维护，并且对于某些类型的引用完整性可能会出现问题。您已经使用的模型（“每个表的类”）可能应该是您的默认选择，除非有令人信服的理由......
This looks like a "subclass" (aka. "category") hierarchy. If you have and always will have only
AMPSand no other "child class", then you may consider merging it withProduct. Otherwise, it looks good.BTW, there are 3 ways to implement class hierarchies in relational databases, each with strengths and weaknesses. Keeping all classes in a single table is one of them, but can be hard to maintain and can be problematic for certain kinds of referential integrity. The model that you are already using ("class per table") should probably be your default choice unless there is a compelling reason otherwise...