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