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?