Re: Database which allows object to be "child" of any other object

On Apr 9, 1:54 pm, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
> kuronekoy..._at_gmail.com wrote:
> > I'm working on a database-driven project and I'm trying to do some re-
> > architecting of the system.
>
> > I would like to cut down on the number of strictly-defined complex
> > object types, instead forming them from a composition of attached
> > atomic metadata objects.  A user could have an attached phone number,
> > an attached address, an attached photo; but furthermore, an address
> > could have an attached photo or phone number.
>
> > This means that in the DB, my relationship cannot be simply from the
> > "PhoneNumber" table to the "User" table.  It must allow relationships
> > from each metadata table to, potentially, every other metadata table.
> > This **could** be done using n^2 association tables for each parent/
> > child combination of metadata tables, but that would suck major donkey
> > balls.  It could also be done by having a generalized association
> > table which specifies both the table *and* the key of the parent and
> > child in the relationship, but I can think of no way to set up
> > automatic referential integrity checking when doing this.
>
> > My questions are:
>
> > * Is this a viable approach?  Has it been done before, is there a
> > common name for it?
>
> If I am not mistaken, a name used for it before was "universal
> relation", but I could be wrong.

Thanks. That's a good starting point for me to search.

> > * Is the "generalized association table" idea compatible with
> > automatic referential integrity checking?  If so, how?
>
> Not really. Think about it.

I have thought about it and come to that conclusion. I was just hoping somebody else might have a more satisfactory answer. :P

It's likely that the separate generalized association table is not necessary, because each metadata item can have only one parent and thus would be in a 1-1 relation with the association table. So rather than having a separate association table, the metadata-item tables could store their parents' table and key, or whatever method is used for addressing the parent. But that still runs into the same issues with breaking referential integrity between the child and the parent. I hope there's a solution for this that both maintains referential integrity and doesn't involve n^2 association tables, but the more I investigate, the more skeptical I become.

> > * Are there any systems out there implemented in this way that I could
> > examine for research, open-source or not?
>
> If I am right about the universal relation thing, this article from 1988
> mentions two:http://portal.acm.org/citation.cfm?id=624712
Received on Thu Apr 09 2009 - 20:25:58 CEST