Skip to main content
2 of 3
added 535 characters in body
Walter Mitty
  • 4.4k
  • 21
  • 22

If you look up "class table inheritance", you will get numerous papers that cover table design for supeclass/subclass situations. My favorite write up is Martin Fowler's. You already seem to know most of what these papers will cover.

If you look up "specialization/generalization", you will get several writeups that address how the ER model was extended to cover the case that we call superclass/subclass. The ER model doesn't help you with design issues, because the ER model is intended to be design agnostic.

And, of course, the object model has inheritance.

If you lookup "shared primary key" , you will get some writeups that mostly confirm the design choices you outlined in the question.

The Stackoverflow area has three tags that group relevant questions. They are named Single-table-inheritance, Class-table-inheritance, and Shared-primary-key. Tags have a "Learn more" feature that will show you a writeup about the tag subject matter. This can help as well. This area does not have these tags.

Your best bet is probably to use Postgress specific features intended to address superclass/subclass cases, provided you can get around the limitations of your ORM tools. Other answers have addressed this approach.

Walter Mitty
  • 4.4k
  • 21
  • 22