Re: Help with complex db design concepts
Date: 9 May 2002 10:28:58 -0700
"Carl Rosenberger" <carl_at_db4o.com> wrote in message news:<abc9gd$ktq$04$1_at_news.t-online.com>...
> zaq wrote:
> > 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.
> > 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'..
> I don't think your approach will perform. Relational databases never work,
> if you use one single table for multiple completely different objects.
> You will also be facing a hell of a lot of outer join trouble.
> For me your problem sounds like a typical object database application.
> Kind regards,
> Carl Rosenberger
> db4o - database for objects - http://www.db4o.com
If you read the original post, you'll notice that proposed design has a discrete table for each spatial entity. The DOC_RELATION table describes only the association between documents and spatial entities, not the spatial entities themselves. Any decent relational database could easily handle this approach and would likely out-perform any object database.
The potential problem with this approach is the temptation to provide document/entity specific attributes within the DOC_RELATION table. You then end up with an undesirable situation in which the DOC_RELATION atribute is only applicable for certain specific spatial entities. As long as DOC_RELATION represents only the association, everything should work fine.
Russ Received on Thu May 09 2002 - 19:28:58 CEST