Re: Help with complex db design concepts

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.

[snip]

> 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

---
Carl Rosenberger
db4o - database for objects - http://www.db4o.com