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