vendredi 12 janvier 2018

What do I call this kind of database design pattern?

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.

Aucun commentaire:

Enregistrer un commentaire