Suppose you have 3 classes: ClassA, ClassB and 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 ClassA, ClassB or a ClassC object. A ClassA/ClassB/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 (
ClassAID,ClassBID,ClassCID) to the Message table. e.g. If a message belongs to aClassAobject, thenClassAIDwill have that obect’s ID as a value andClassBIDandClassCIDwill be null. This will force me to write a constraint/trigger to make sure that theMessageobject 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
ClassA,ClassBandClassC. 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.
EDIT 1:
Imagine the Message objects as being comments (or files) in a tree-like structure of objects (ClassA objects can have ClassB‘s and 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 ClassA or 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.
EDIT 2:
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
EDIT 3:
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
5
What you have called “Table Per Hierarchy” resembles what Martin Fowler and others have called “Class Table Inheritance”. See Diagram. I would call this mimicking inheritance rather than implementing inheritance.
One technique that can be used to good advantage in some cases of class table inheritance is called “shared primary key”. See tag wiki in SE.
The advantage if shared primary key is that an FK reference that references the shared primary key can reference both the entry in the generic class table and the entry in the appropriate specialized class table. This turns out to be really handy, and can justify the extra programming needed to implement shared primary key.
2
It makes a difference of what ClassA and ClassB really represent – generic names are not helpful for making good design decisions. For example, if those are things which can or should be modeled with a common base class, with a real “is-a” relationship, go that route.
If ClassA and ClassB have nothing in common except those message, then inheritance is probably not the best approach. For this case, your solution 1 and 3 are both possible. Which one is more adequate depends on how “waterproof” you need your database, and what kind of access patterns and queries you expect.
1
Going with that last option in your edit but altering it a little.
In this, you abstracted away any changes in concrete class.
In your database however, you are saving the value of an enum that saves which kind of concrete class you are referring to, and what that concrete class’ id is.
This info can be passed to the factory, which should provide a method returning a genericClass which can be any of the concrete classes based on that previous enum and id.
- Message
+ int Id
+ int TypeEnum
+ int ClassId
- Class
+ int Id
+ int TypeEnum
Class.TypeEnum would the the “loose” constaint on Message.TypeEnum.
This way you don’t need to worry about needing to add columns for additional nullable foreign keys.
As I’ve pointed out: you will have to take into account that the database constraints (Foreign Keys) between the Message-class and ClassA, ClassB, … ClassX cannot be used and should be taken care of manually.
It sounds like you’re having exactly the same kind of problem that led to me adopting an object database rather than relational in one of my current projects.
Using an object database, you could simply have Message holding a non-nullable reference to MessageClass object, with ClassA, ClassB and ClassC being subtypes of MessageClass. While this is basically the same as one of your suggestions, the simple fact is that while this is difficult to implement using SQL (and impossible to get correct referential integrity constraints for, if the different classes require different types of data) it is trivial to do in an object database.
I’m using db4o and finding it quite handy for this kind of task.