Help with complex db design concepts

Consider a database where my tables represent spatial entities, such as buildings, roads, utility poles, bodies of water, etc... This set of entities is fixed, but over 1000.

Now consider a database designed to associate documents with one or more of these entities. Users will need to search all documents, as well as search for documents associated with spatial objects.

My solution is to create a single table for documents and a single table intented to handle the many to many relationship between documents and ALL spatial tables. The relation table would like something like this:

DOC_RELATION (document_id, spatial_entity, spatial_entity_id). In this case spatial_entity would equal the spatial entity table name.

So... I would join the DOC_RELATION to BUILDINGS where document_id=buildings.spatial_entity_id ONLY WHEN spatial_entity='BUILDINGS'..

Then I could have a view set up for this called "DOC_BUILD_XREF" that only includes records where spatial_entity = 'BUILDINGS'

I know this is difficult, but I'm trying to find a innovative way to glue this enterprise-level applciation together.

Thoughts, discussions?

Thanks in advance!

Zaq Received on Thu May 09 2002 - 00:16:51 CEST