Re: Help with complex db design concepts

From: <paul.vernon_at_ukk.ibmm.comm>
Date: Thu, 9 May 2002 13:40:31 +0100
Message-ID: <abdr69$j3s$1@sp15at20.hursley.ibm.com>

In principle, I would create a union view over the common attributes available in your 1000 spatial entities. E.g.

CREATE VIEW ALL_SPATIAL (spatial_entity, spatial_entity_id, name, area, ...)AS SELECT 'BUILDINGS', spatial_entity_id, bld_name, width * length, ... FROM Buildings
UNION ALL
SELECT 'ROADS', spatial_entity_id, road_name, NULL, ... FROM Roads
UNION ALL
SELECT 'BODY_OF_WATER', spatial_entity_id, name, area, ... FROM Bodies_of_water
/* continue for all 1000 entites */

Then join to DOC_RELATION on spatial_entity and spatial_entity_id where spatial_entity is restricted to the set of entities any given query is interested in.

The query optimiser in your DBMS should eliminate any unneeded access to the separate spatial entities, so performance should be fine.

Simple (in principle).

Regards
Paul Vernon
Business Intelligence, IBM Global Services Received on Thu May 09 2002 - 07:40:31 CDT