Re: Multiple tables refer to one -To use foreign keys or not?

On 30 mayo, 06:11, "Roy Hann" <specia..._at_processed.almost.meat> wrote:
> "Adriano Varoli Piazza" <mora..._at_gmail.com> wrote in messagenews:1ff6aed6-4173-4f05-a723-96a3a9228344_at_z72g2000hsb.googlegroups.com...
> If I understand your intention correctly then I am sorry to say it probably
> is a WTF.  I say "probably" because you actually describe two intentions.
> In one place you talk about relating objects (sic) to attachments, and
> immediately after that you talk about doing the opposite.

My bad. Attachments belong to objects, not otherwise (at least, not in this design).

> I suspect you are uncomfortable with the idea of multiple attachment tables
> because superficially they would look the same (in the sense they'd have the
> same column names, each of the same type).  That is a programmer's point of
> view.  To a database designer they should appear to be VERY different and I
> suspect that if you look deep into the logic of what one is justified in
> doing with attachments for people versus attachments for works_of_art, those
> differences will become apparent.  Once you see that you  will be perfectly
> happy to have multiple attachment tables.  The physical model would
> correspond closely to the logical model.

This is a _very_ useful and enlightening answer. Yet again, by your words below...

> That makes life easy because it makes it hard to write a correctly formed
> query that executes correctly and yet gives a wrong or meaningless answer.
> Database design is not about making it easy to do correct things so much as
> it is about making it hard to inadvertently do wrong things, and rather
> paradoxically (it seems to me) that often ends up making correct things easy
> into the bargain.
>
> Having offered an opinion, I ought to say you'd be unwise to follow any
> specific advise you get from a newsgroup.  We all have our own enthusiasms,
> prejudices, and preconceptions of what you are trying to do.

I'm trying to inform myself, and I have to do it on the run (i.e. not enough time to do it right and study SQL properly, plenty to do it over :) ).
So I'll take my chances. Of course, I do try to think before I implement anything.

Thanks.
Adriano Received on Fri May 30 2008 - 12:29:38 CEST