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

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.

Saludos,
Adriano
.

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)
  • Re: Strip inbox details and populate db
    ... Easy enough to do using ADO code. ... them to the file system and then opening the files using something like ... tests it would be very similar, just a different connection to the SQL ... > outlooks inbox as well as any attachments. ...
    (microsoft.public.outlook.program_vba)
  • 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 ... Database design is not about making it easy to do correct things so much as ...
    (comp.databases.theory)
  • Re: Why wont this work in a update query
    ... If [Attachments] is a Text Field, the first argument of the IIf is incorrect ... Either way (Text or Boolean Field), you will have errorwith the Update ... SQL due to data type mismatch! ... filed not a Y/N datatype ...
    (microsoft.public.access.queries)
  • Re: UPDATE QUERY PLEASE HELP!
    ... I explained the problem with the Update SQL in the orginal thread: ... doing a Boolean-value comparison on the Field and then ... The Field [Attachments] canNOT be ... In the Select SQL, the IIf expression is a Calculated Value only (not being ...
    (microsoft.public.access.queries)