Class Table Inheritance with a hierarchy

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?

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:

Discussions ----> Messages ---> Commnts