Re: Reasons for creating linking tables?

On Mon, 15 Nov 2010 14:11:12 +0200, Machiel Richards <machielr(at)rdc(dot)co(dot)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