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

From: David Cressey <cressey73_at_verizon.net>
Date: Mon, 02 Jun 2008 19:18:31 GMT
Message-ID: <bIX0k.2106$BV.258@trndny05>

"Adriano Varoli Piazza" <moranar_at_gmail.com> wrote in message news:1ff6aed6-4173-4f05-a723-96a3a9228344_at_z72g2000hsb.googlegroups.com...
> 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.
>

Don't do this. It's been tried thousands of times. It nearly always leads to chaos.

The general principle is: don't put data and metadata side by side in the same table.

I have violated this principle in my own private tools, but never in work to be used by others. Received on Mon Jun 02 2008 - 14:18:31 CDT

Original text of this message