Re: Multiple tables refer to one -To use foreign keys or not?
Date: Fri, 30 May 2008 10:11:12 +0100
Message-ID: <-cidnUq9ccosWaLVRVnyjgA_at_pipex.net>
"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.
>
> 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.
Roy Received on Fri May 30 2008 - 11:11:12 CEST