具有层次结构的类表继承
我有 3 种数据类型——a) 公共列 b) 层次结构——并且想知道如何设计数据库。
假设在顶层有一个讨论。其中包括讨论消息、文件和评论。它们都有共同的字段（UserID、CreateDate、Text），但也有独特的列（FileName、ContentType 等）。注释可以与任何类型（甚至其他注释）关联，并且与文件相同。
在研究了这里的一些问题之后，我想我想要类表继承——一个基表（DiscussionParts）来存储公共元素，然后是每种数据类型的特定表。
讨论部分：PartID、DiscussionID、ParentID、PartType（M、F、C）、UserID、Text、CreateDate
消息：MessageID、PartID、标题
文件：FileID、PartID、文件名、ContentType
评论：CommentID、PartID、来源
ParentID 列用于跟踪层次结构。 DiscussionID 使用户可以通过一个简单的查询搜索他/她的所有讨论中的文本。
这是具有层次结构的类表继承的推荐设计吗？
I have 3 data types with -- a) Common columns b) a hierarchy -- and would like to know how to design the database.
Let's say at the top level, there is a Discussion. Within that, there are DiscussionMessages, Files and Comments. They all have fields in common (UserID, CreateDate, Text), but also have unique columns (FileName, ContentType, etc.) Comments can be associated with any type (even other Comments) and same with Files.
After looking into some Questions on here, I think I want Class Table Inheritance -- one base table (DiscussionParts) to store the common elements, then specific tables for each data type.
DiscussionParts: PartID, DiscussionID, ParentID, PartType (M,F,C), UserID, Text, CreateDate
Messages: MessageID, PartID, Title
Files: FileID, PartID, FileName, ContentType
Comments: CommentID, PartID, Source
The ParentID column is to keep track of the hierarchy. The DiscussionID is so that a user can search all his/her discussions for text in one simple query.
Is this the recommended design for Class Table Inheritance with a hierarchy?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我建议根据实体和可能的功能而不是类来重新构建它。原因是数据库不执行“类”和“继承”，并且以这种方式转换它们可能会导致低效的设计。
听起来像是，“……在顶层，有一个讨论。”意味着这是您的顶级表格，大概有 ID、标题，也许还有一些描述消息内容的文本。
从那里，您的 Messages 表是 Discussions 的子表，其中 DiscussionId 作为外键，其 messageId、标题、文本等。听起来你正在建立一个论坛，到目前为止一切顺利。
评论有点棘手。大概评论是添加到消息中的？这意味着您的 Comments 表有一个消息外键，加上它自己的 CommentId 和一些文本。
但从这个问题我无法弄清楚文件来自哪里。它们是否附加到整个讨论中？一条消息？或者评论？无论答案是什么，请使“文件”表成为相应表的子表。
所以你的层次结构不是类的，而是表的，看起来像：
I would suggest recasting this in terms of entities and possibly functionality rather than classes. Reason is that databases do not do "classes" and "inheritance", and casting them that way can lead to inefficient design.
It sounds like, "...at the top level, there is a Discussion." Means that this is your top table, presumably having an ID, title, and perhaps some text that describes what the message is about.
From there, your Messages table is a child of Discussions, with a DiscussionId as foreign key, its messageId, Title, text, and so on. Sounds like you are building a forum, so far so good.
Comments is a bit tricky. Presumably comments are added to Messages? This means that your Comments table has a foreign key to messages, plus its own CommentId and some text.
But from the question I cannot work out where files come in. Are they attached to a Discussion as a whole? A Message? Or a Comment? Whatever the answer to that, make the Files table a child of the appropriate table.
So your hierarchy is not of classes, but of tables and looks like: