Implementing subtype of a subtype in type/subtype design pattern with mutually exclusive subclasses

Introduction

In order for this question to be useful for future readers I will use the generic data model to illustrate the problem I face.

Our data model consists of 3 entities, which shall be labeled as A, B and C. In order to keep things simple, all their attributes will be of int type.

Entity A has following attributes: D, E and X;

Entity B has following attributes: D, E and Y;

Entity C has following attributes: D and Z;

Since all entities share common attribute D, I have decided to apply type/subtype design.

Important: Entities are mutually exclusive! This means that entity is either A or B or C.
Problem:

Entities A and B have yet another common attribute E, but this attribute is not present in the entity C.
Question:

I would like to use the above described characteristic to further optimize my design, if possible.

To be honest, I have no idea how to do this, nor where to start trying, hence this post.

According to Martin Fowler, there are 3 approaches to the problem of table inheritance:

• Single Table Inheritance: One table represents all types. Unused attributes are NULLed.

• Concrete Table Inheritance: One table per concrete type, each table columns for each attributes of the type. No relation between tables.

• Class Table Inheritance: One table per type, each table has attributes only for new, not-inherited attributes. Tables are related reflecting the actual type inheritance hierarchy.

You can start with these as a starting point to search for pros and cons of each approach. The gist of it is that all approaches have major disadvantages, and none has any overwhelming advantage. Better known as the object relational impedance mismatch, this problem is yet to find a solution.

Personally I find that the type of problems a bad relational design can lead to are orders of magnitude more serious than the kind of problems arising from a bad type design. Bad database design leads to slow queries, update anomalies, data size explosion, deadlocks and unresponsive apps, and tens to hundreds of Gigabytes of data sunk in the wrong format. Bad type design leads difficult to maintain and update code, not runtime. Therefore, in my book, correct relational design trumps any OO type purity over and over and over.

StackExchange Database Administrators Q#140604, answer score: 21