7

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 a ClassA object, then ClassAID will have that obect's ID as a value and ClassBID and ClassCID will be null. This will force me to write a constraint/trigger to make sure that the Message object 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).

Nullable FK's

  • Implement inheritance and generalize ClassA, ClassB and 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.

Inheritance

  • Add a ClassToMessage table (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.

Link Table to Message

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...

Table Per Hierarchy

  • 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).

No Relationship

I have seen this issue being addressed in StackOverflow, but I can't seem to find a good solution:

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

  • 1
    I'd actually go for the last option. Let ClassTypeName be defined by some enum. Then your business-logic would be a factory-method or class. Hand that factory your enum and ID values and voilá. Only downside I see in this is that your DB constraints will be non existant, you will need to handle this. – Allmighty Oct 29 '14 at 10:00
  • 1
    possible duplicate of Database Design for Inheritance – Walter Mitty Oct 29 '14 at 10:52
  • @WalterMitty not really, as this is not a question specifically about inheritance. Inheritance is only one of the ways I describe as a possible solution. – user1987392 Oct 29 '14 at 10:58
  • 1
    Well, the question pertains to table design and also to IS-A relationships. If you ask me, that means that the lack of inheritance in the relational database model is central to the question. – Walter Mitty Oct 29 '14 at 11:37
1

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.

  • So far, Table Per Hierarchy / Class Table Inheritance on the Message class seems like the way to go and I'm now addressing the issue in a more specific (inheritance & EF6) way on StackOverflow. I'm still not sure on how practical it will be to navigate from a Message/Comment to the Object that holds it, etc. (using EF, that is), but that's already out of scope for this community. Thanks. – user1987392 Oct 29 '14 at 15:37
  • You might want to try the DBA site for database questions. – Walter Mitty Oct 30 '14 at 8:46
1

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.

  • Hey Doc, thanks for the input. The three classes have a parent-child-grandchild relationship (A has B's, B has C's) and you can imagine the Messages as being comments on that structure. "you could just add a MessageID to ClassA and ClassB and handle the requirement of not having the same message referenced outside of the database" → Not quite, as it's a one-to-many relationship where each A/B/C instance can have 0 or many messages and a message must belong to one and one object (either from ClassA, ClassB or ClassC). By adding MessageID to the ClassA/B/C it becomes a one-to-one. – user1987392 Oct 28 '14 at 19:25
1

Going with that last option in your edit but altering it a little.

http://yuml.me/adbd9b9e.png

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.

-1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.