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

Adriano Varoli Piazza wrote:

> 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

I will cite Date's _Principle of Incoherence_ and ask whether you are re-inventing EAV, yet again? Received on Thu May 29 2008 - 23:38:31 CEST