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?
* Is the "generalized association table" idea compatible with
automatic referential integrity checking? If so, how?
* Are there any systems out there implemented in this way that I could
examine for research, open-source or not?

kuronekoyama (AT) gmail (DOT) com wrote:

If I am not mistaken, a name used for it before was "universal
relation", but I could be wrong.


Not really. Think about it.


If I am right about the universal relation thing, this article from 1988
mentions two: http://portal.acm.org/citation.cfm?id=624712

On Apr 9, 1:54*pm, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:
Thanks. That's a good starting point for me to search.

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.

Bob Badour wrote:
I'm not sure either because I seem to remember from years ago Ullman and
maybe Kent writing about something more exotic than this quote from Date
(pg. 420, Logic and Databases):


"Given a relation type RELATION {H}, where {H} is a heading, the
universal relation of that type is the relation with heading {H} and
body consisting of all possible tuples of type TUPLE{H}."


By "all possible tuples", I think he is excluding any additional
constraints on a relation, beyond the ones that the attributes make,
eg., if a candidate key has fewer attributes than {H} that constraint
has to do with some relation value, not with the universal relation of
the relationn type.

On Apr 9, 9:37*am, kuronekoy... (AT) gmail (DOT) com wrote:
I understand that "metadata object" is "object in RDBMS dictionary".
The latter is just a table, correct?

I'm not quite sure what "strictly defined" means. Also what are
"complex object types", the structures composed of complex numbers?

Here is my revolutionary contribution to entity-relation theory. If a
table has "photo" column, that's an entity. It is relationship,
otherwise. Wait a minute "marriage" can have photos attached too, so
never mind.

Ok, I missed april 1st by quite a margin, so I suggest you approach
your problem from slightly different angle. This of the following
queries "find all the users by the phone number", "find all the users
at the specified address", ... Are they easily expressible in your db?
Are you actually getting any benefits abandoning traditional database
design?

On Apr 10, 1:54 am, Bob Badour <bbad... (AT) pei (DOT) sympatico.ca> wrote:
That doesn't seem right to me. The UR that Ullman championed would
require a distinct attribute name for each distinct role, so therefore
for example, a photo attached to an address has a different role from
a photo attached to a user. Therefore this approach would lead to a
quadratic order proliferation of attribute names.