Suppose you have 3 classes:
ClassC. Now suppose you have a class
Message. You want a
one-to-many relationship between each one of those three classes and the Message.
The goal is that a
Message must 'belong' to either a
ClassB or a
ClassC object. A
ClassC object can have zero or many
Messages. The Message class is the same for any of the three classes (i.e. there shouldn't be a different class of Message for each of the three Classes A, B and C).
Now thinking about data-modeling on a relational DB (also, I'm using ORM, in this case Entity Framework Code First), I'll map each one of those 3 classes and the
Message class to a table.
But then, the issue is trying to apply the relationships I explained above. I see a few ways to do this:
- Add three nullable Foreign Keys (
ClassCID) to the Message table. e.g. If a message belongs to a
ClassAIDwill have that obect's ID as a value and
ClassCIDwill be null. This will force me to write a constraint/trigger to make sure that the
Messageobject only has a value in one of those FK's. It also makes it a bit ugly on the Business Logic and Presentation layers whenever I want to know to which object does a message belong to: the MessageObject would have a nullable reference to a ClassA, ClassB and a ClassC object and I would need to check it before presenting it in the UI (e.g. a link from the Message to its object).
- Implement inheritance and generalize
ClassC. The Message has a FK for the generalized class' ID. This adds complexity and doesn't really solve the problem of easily figuring out to which ClassA/B/C a Message is related to.
- Add a
ClassToMessagetable (similar to a link-table) that connects the 3 tables to the Message one, again, via nullable FK's. Sort of a combination of the previous two, but still not exactly a good solution for the same reasons.
I'd like to know if there's an elegant way to achieve the desired scenario, preferably having the Entiy Framework approach in mind. Thanks in advance.
Imagine the Message objects as being comments (or files) in a tree-like structure of objects (
ClassA objects can have
ClassB's can have
ClassC's...). You want objects from all of those classes to have zero or many comments. You want the same Comment table for all of the comments (doesn't matter if they're belong to a
ClassB... instance). You also want it to be easy to find out the comments for a given object or the object a comment belongs to.
Two other alternatives:
- Implement table per hierarchy (TPH) by having a ClassAMessage, ClassBMessage... that still doesn't quite solve the problem...
- The Message stores the ID of the object that owns it and the object Type (whether it was a ClassA, ClassB...). No actual relationship: service layer or business logic does the rest (ouch).
I have seen this issue being addressed in StackOverflow, but I can't seem to find a good solution:
- Entity Framework Code First - Entity maps to one or the other
- Multiples Object Relationship in Code First
- Database design - articles, blog posts, photos, stories
I'm pursuing alternative #4 (Table per Hierarchy / Class Table Inheritance) and have evolved this into a more specific S.O. question: Entity Framework 6 Code First - Comments on a Tree Structure