Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Help with complex db design concepts

Help with complex db design concepts

From: zaq <zaq42_at_yahoo.com>
Date: 8 May 2002 15:16:51 -0700
Message-ID: <185bc2bb.0205081416.bd80df4@posting.google.com>


I have a problem I've not face in my ten years of database design. I have an idea for the solution, but really want feedback.

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 Wed May 08 2002 - 17:16:51 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US