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

kuronekoyama_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.

> * Is the "generalized association table" idea compatible with
> automatic referential integrity checking? If so, how?

Not really. Think about it.

> * 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 - 19:54:15 CEST