Обсуждение: Reasons for creating linking tables?

Good day all

    I would like to get the point of view from you guys out there who has had a lot of database experience.

    While busy looking into a database that have a lot of issues I have found the following  scenario :

        - Let's call the 3 tables in question tables a,b & c.
        - Currently table a has got a FK referencing table b
        - table b contains only 1 field called id which is the PK and also a FK referencing table c
        - table c is once again a table with a couple of fields.


    My question on this is, why create the linking table?

    Surely the linking table can be removed and the data referenced directly between tables a & c?

    Is there a specific reason why this would need to be done?


Regards
Machiel

       



 

* In this specific case, frankly, I can't see none...

It seems you have a relation Many-to-One between table a and c.

I usually place an associative table in the middle when I have a many-to-many relationship, which is not the case.

 

In this scenario I would consider table b superfluous (specially when it has no other fields except its ID) and short circuit it.

 

That's my oppinion

 

Best,

Oliver

       

Thank you,


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.



-----Original Message-----
From: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
To: Machiel Richards <machielr@rdc.co.za>, pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Reasons for creating linking tables?
Date: Mon, 15 Nov 2010 12:26:19 -0000



    I would like to get the point of view from you guys out there who has had a lot of database experience.

    While busy looking into a database that have a lot of issues I have found the following  scenario :

        - Let's call the 3 tables in question tables a,b & c.
        - Currently table a has got a FK referencing table b
        - table b contains only 1 field called id which is the PK and also a FK referencing table c
        - table c is once again a table with a couple of fields.


    My question on this is, why create the linking table?

    Surely the linking table can be removed and the data referenced directly between tables a & c?

    Is there a specific reason why this would need to be done?

 

* In this specific case, frankly, I can't see none...

It seems you have a relation Many-to-One between table a and c.

I usually place an associative table in the middle when I have a many-to-many relationship, which is not the case.

 

In this scenario I would consider table b superfluous (specially when it has no other fields except its ID) and short circuit it.

 

That's my oppinion

 

Best,

Oliver

       

On Mon, 15 Nov 2010 14:11:12 +0200, Machiel Richards <machielr@rdc.co.za>
wrote:


Hi,

>     I would like to get the point of view from you guys out there who
> has had a lot of database experience.
>
>     While busy looking into a database that have a lot of issues I have
> found the following  scenario :
>
>         - Let's call the 3 tables in question tables a,b & c.
>         - Currently table a has got a FK referencing table b
>         - table b contains only 1 field called id which is the PK and
> also a FK referencing table c
>         - table c is once again a table with a couple of fields.
>
>
>     My question on this is, why create the linking table?
>
>     Surely the linking table can be removed and the data referenced
> directly between tables a & c?
>
>     Is there a specific reason why this would need to be done?

Think: invoice, RMA & credit note (and many other things) - if you avoid
the b table you'll be obliged to put the reference in a &| c
(obviously:) BUT they'll only be one time into a table.

Now think to a not-so-often-but-existant case:
you sell a product that fails,
you issue an RMA to the customer
and refund him making a credit note (or directly exchange the product)
and ask your supplier to replace the product
if you don't have table b, how will you get a back tracking from the new
product to the old one?

And you can even have a worse case: the new product can fail again,
relaunching a whole "cycle".

In short, tracking case like that *need* the b table to avoid
Gordian knots to appear into your design (ie: you put 2 references in
table a or c, but you have 3 failures.)

JY
--
Nothing succeeds like excess.
        -- Oscar Wilde

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