What do I call this kind of database design pattern?

Question

Foreign Keys are generally of the form:

A <━━ X

(Meaning that X references A, a one to many relationship)

But now, there's a new thing that also can have an X.

A nieve representation might look something like this:

A <━┳━ X
B <━┛ 

In this case, X would have two foreign keys, one of which would be null. I personally hate nulls (as should everyone?), so this is unacceptable to me.

The next best thing might look something like this:

       X
       ^
       ┣━━━━━━┓
       ┃      ┃
       ┃    ┃
A <━━ AX      ┃
              ┃
B <━━━━━━━━━ BX

So in this case, AX and BX are two associative tables (which IMO should be referencing X by a key to make them have 1-to-1 relationships with X to help enforce the same constraints as before)

And while this works well enough, it becomes overly complicated when I add table C and D which also have an X.

So the next model I came up with looks something like this:

hX <━━ X
 ^
 ┃
 ┣━━━━━┳━━━━━┓
 ┃     ┃     ┃
 ┃    ┃   ┃
 A      B    C

So I really like this model, but I don't really know what to call it. Is this a standard pattern? Is there another better pattern to solve this problem? Where can I read more about something like this?

If it's not a standard pattern, what should I call the "hX" table? Associative tables typically just concatenate the two table names together, but in this case it's not an associative table, partially because there's 3 different references.

I could call it a "CanHaveX", but that's getting a little wordy

"XAnchor" seems kind of close, but what I really want is the "XAnchorPoint"?

I personally like "XCleat" (because one puts a cleat on something they want to have something else stay with it), because this allows the table A to have an X reference it, but the term is a little weird.

Answer 1

TL;DR You don't give any meanings for why the FKs (foreign keys) hold but it seems likely A, B & C value/entity types are subtypes of hX's. Google SQL/database subtypes/inheritance/polymorphism. Multiple nullable FKs is a common antipattern frequently also described as FKs [sic] to multiple tables. One name for this is Class Table Inheritance. On the other hand we can define such a kind of subtype whenever a FK holds so there's not necessarily anything important going on in business/application terms just because you see a tree of FKs. It's not clear what you think your justfication for "overly complex" is. Tables represent n-ary business/application relation(ship)s/associations and in your case certain values/entities are As, Bs and/or Cs and are also all related per X to other values/entities.

---

The rest of this answer explains how to move from a design like your original antipattern with nullable FKs to one without in the general case.

An SQL FK (foreign key) is a set of columns where non-null subrow values appear elsewhere as PK/UNIQUE. Every FK should be either declared or follow from FKs & other constraints that are declared. (Usually undeclared FKs follow from declared FKs by transitivity.)

If you don't want a table with nullable columns then you need multiple tables. This is orthogonal to what your FKs are. One table is like the original with the column(s) dropped, holding the projection of the original on all other columns. The other tables have some original table CK (candidate key) columns plus some column(s) that you didn't want nullable, holding subrows of the original where the column is not null. Notice that the CK in the second is a FK referencing the CK of the first. And that that CK FK was not one that was nullable in the original table.

Constraints follow from table meanings plus what business/application situations can arise. It just happens that two tables with same CK values can be replaced by their join and vice versa. Also, if a first has a superset of the CK values of a second then you can replace them by their left join and vice versa. This happens to be when there is a FK on the CK from the second to the first. This reverses the null-elimination above. It has nothing to do with whether any columns in the second that get nulls are themselves FKs. When we should or shouldn't or can or can't use multiple tables vs their join is the topic of normalization to higher NFs (normal forms).

There isn't any particular pattern of design that is imposed by specifically eliminating nullable FKs. Just use proper information modeling & database design principles, including normalization, including identifying relevant constraints, to find tables/meanings that record what you want. Then identify further constraints. Then write declarations.

What to do with null values when modeling and normalizing?
What is the difference between an entity relationship model and a relational model?