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

"Adriano Varoli Piazza" <moranar@xxxxxxxxx> wrote in message
news:1ff6aed6-4173-4f05-a723-96a3a9228344@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
As one of my first encounters with SQL (The RDBMS used is MySQL 5.1),
my colleague and I have designed the following (it's a part of a
catalog system for a museum):
We have an "attachments" table, which can refer to "works_of_art",
"people", "reproductions" (Let's say, more than three tables). So a
work of art can have attachments, a person can have a different
attachment, etc.

To be able to relate each object to its eventual attachments, one
solution I imagined is to put N foreign keys from each of the tables
referred in attachments (if my use of the verb refer is incorrect,
please do point it out). Let's say that, for now, there are no fields
exclusive to any of the referred tables, so creating different
attachment tables is not the best obvious choice.

The alternative solution we came up with was to use two fields in
"attachments" to identify the relations: one with the id of the
referred table, and one with the "type". So 'works of art' would be 0,
'people' would be 1... The reasoning behind the decision was that my
colleague felt that too many fields would remain empty with the first
solution, and that adding a new type of "attachee" was simpler/more
efficient than adding a new foreign key.

This solution doesn't strike me as the best. We want to use cakephp to
build the app that will use the database, and while this solution
would eventually work with some manual labour, I feel it grates
against it when the rest of the design does not. Since my knowledge of
SQL is tenuous at best, I wanted to listen to some informed opinions
about this before arguing again.

By all means, if this is a WTF, point it out, the reason, and possibly
an alternative.

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.

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.

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.

Roy


.

Relevant Pages

  • Re: Multiple tables refer to one -To use foreign keys or not?
    ... To be able to relate each object to its eventual attachments, ... So 'works of art' would be 0, ... colleague felt that too many fields would remain empty with the first ... against it when the rest of the design does not. ...
    (comp.databases.theory)
  • Multiple tables refer to one -To use foreign keys or not?
    ... As one of my first encounters with SQL, ... To be able to relate each object to its eventual attachments, ... colleague felt that too many fields would remain empty with the first ... against it when the rest of the design does not. ...
    (comp.databases.theory)
  • Re: Multiple tables refer to one -To use foreign keys or not?
    ... To be able to relate each object to its eventual attachments, ... So 'works of art' would be 0, ... colleague felt that too many fields would remain empty with the first ... The general principle is: don't put data and metadata side by side in the ...
    (comp.databases.theory)
  • Re: watering system, second attempt.
    ... > CMOS ICs and a mosfet to switch the coil. ... > design i've come up with. ... but then a chip failed and i'm waiting to get another one. ... html mail or attachments will go in the spam ...
    (sci.electronics.basics)
  • Microsoft Outlook 2003 - Forms
    ... I have designed myself a form for a mail message that I will be using all ... forms library form, can you not design it to allow you to add attachments to ...
    (microsoft.public.outlook)