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

If I understand your problem correctly then the constraints that you desire are called "distributed foreign keys" and they're described at http://www.dcs.warwick.ac.uk/~hugh/TTM/Missing-info-without-nulls.pdf The snag is that no DBMS on the market today supports them in a simple declarative way like they do with the SQL "FOREIGN KEY" syntax. You can implement them using triggers, which is described in:

Koppelaars, T. and de Haan, L. Applied Mathematics for Database Professionals. Apress. 2007.

Regards,

Adrian

"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.
>
> Saludos,
> Adriano
Received on Tue Aug 19 2008 - 23:43:57 CEST