-1

I have various 'named objects' that have some shared properties and I think it might make the most sense to save them in the following format in my database:

  • NamedObjectWrapper

    • ID
    • CreatedBy
    • Name
    • ...

    (here is my tagged union)

    • NamedObjectType
    • NamedObject1 (FK NULLable)
    • NamedObject2 (FK NULLable)
    • NamedObject3 (FK NULLable)

  • NamedObject1

    • ...

  • NamedObject2

    • ...

  • NamedObject3

    • ...

Exactly one of NamedObject1, NamedObject2, and NamedObject3 will be set, and the other two null. Is the above an OK way to do this? I suppose the more-relational way to do it, by just storing three tables with all the repetitive properties is fine, but I'll be UNION-ing those three tables together all the time, so I think this saves me a lot of work doing it this way.

David542
  • 104,438
  • 178
  • 489
  • 842
  • Setting all but one FK to null is a faq & usually an anti-pattern for DB/SQL subtypes/inheritance which is also a faq & best done by variations on a supertype table plus subtype tables or the left join of those. – philipxy Nov 22 '21 at 02:16
  • @philipxy what does `faq` mean? – David542 Nov 22 '21 at 02:18
  • I mean what the acronym stands for but not the official site FAQ. – philipxy Nov 22 '21 at 02:24
  • Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Nov 22 '21 at 02:29

1 Answers1

0

Instead you can FK on the detailed objects to the shared information stored on a base object. For example:

NamedObjectBase

  • ID
  • ...

NamedObject1

  • BaseID (FK)
  • ...

NamedObject2

  • BaseID (FK)
  • ...

NamedObject3

  • BaseID (FK)
  • ...
David542
  • 104,438
  • 178
  • 489
  • 842
  • 1
    From the [Book graph Answer](https://stackoverflow.com/a/70518591/484814), hopefully, you realise this Q is resolved by a simple, classic, Subtype cluster, with full integrity. Of course, you need to rip the do-nothing `ID` fields out, along with its additional do-nothing index. – PerformanceDBA Dec 31 '21 at 05:02