foreign-keysrelational-database

Databases with Foreign Keys: Good way to picture forward vs. backward relationship?


In any relational database system, we can have both a parent model and a child model (which stores a foreign key of the parent).

While I work with these just fine in practice, the way these are often described as a "forward relationship" or "reversing/backref/related_name/going backward" always itself seems backwards to me!

For example, many sources and articles describe going from the child to the parent as the "forward foreign key." But when I picture a tree, I start at the parent node at the top, and I view traversing down it as "forward," which is opposite to how everywhere else describes going from parent to child as a reverse or backward relationship.

What is an explanation of this nomenclature that could help me remember this?

What is a way to picture it?

I haven't found anyone discuss the conventions/terminology (just how to code with it).


Solution

  • In the relational model relation(ship)s/associations are represented by tables. FK (foreign key) constraints are not relationships, though they are wrongly called that. They are constraints, and are not needed to be declared, known or existent to query. A FK constraint states that values appear elsewhere once. Or equivalently, that values/entities participating together in a certain relationship participate together in a certain other relationship once. "Parent" vs "child" apply to any tree-structured or other hierarchically-structured relationship & "forward" vs "backward" are generic terms applicable to any direction of any directed binary relationship--including the meta relationship on tables "has a FK that references" or "appears once at". (Which is not actually how calling FKs "relationships" arose though.)

    There are just common conventions ... including for "trees" ... funny, the trees outside go up from the root, not down. Computer science trees going down is just another convention ... for westerners writing from top to bottom, towards where there's more room. Although there might have been particular reasons for some conventions there's no point in worrying about them making sense or being in agreement.

    We say a FK references a PK so reading left to right as in English we might call that "forward". FKs can form cycles relationally, but SQL DBMSs tend to restrict declarations to trees because they overload them with cascading functionality & don't bother to allow dags or cycles. So in SQL parent is referenced & child is referencing. But SQL FKs are not analogues of relational FKs; they are analogues of what we could call relational foreign superkeys. So there's "FK" overloaded.

    There's no reason to expect consistency. French toast isn't toast. Relationships aren't relationships.

    Don't use these generic terms. Instead, clearly identify the relation(ship)s/associations you are talking about, naming and/or ordering their parameters, and use correct technical terms correctly, like "referencing" & "referenced". Need I say you must memorize definitions of technical terms? Or for generic terms, in line with that, define what specific meaning you are going to use for the generic terms in each specific context. (But you are just asking for misunderstandings.)

    Isn't it wrong to say Parent-Child tables in database design?