Re: Reasons for creating linking tables?

Please don't top-post.

Machiel Richards wrote:
> that is exactly how I was looking at it too, however the specific
> database have loads of these so I thought that maybe there is a reason
> for it and I might be missing something due to limited experiences.

There's limited experience involved, all right, but it isn't yours.

Table "b" in your scenario suffers from two major problems, its existence at
all and its use of an additional, superfluous surrogate key.

As others have pointed out, there is a case for table "b" to track some sort
of history.  However, I suspect that if that were its purpose, table "b" would
be a many-to-many linker, and it would have foreign keys back to both "a" and
"c", not from "a" to "b" as you describe.  On the face of it, absent more
information, I find table "b"'s existence to be of questionable value.

Even if it were valid to have the table, its additional id column with its own
surrogate key, as I infer is the structure, is an antipattern.  Linking tables
generally should avoid having their own keys, and use the concatenation of
their foreign keys (possibly with a discriminator such as a TIMESTAMP column
for history tables).  This is also advisable for dependent tables, those with
a many-to-one relationship back to their antecedent tables.

Even those who favor sequenced surrogate keys should understand when they are
inappropriate.

--
Lew