Re: Help with complex db design concepts

From: Brian Inglis <>
Date: Thu, 16 May 2002 14:40:07 GMT
Message-ID: <>

On Thu, 9 May 2002 13:40:31 +0100, paul.vernon_at_ukk.ibmm.comm wrote:

>In principle, I would create a union view over the common attributes
>available in your 1000 spatial entities.
>CREATE VIEW ALL_SPATIAL (spatial_entity, spatial_entity_id, name, area, ...)AS
>SELECT 'BUILDINGS', spatial_entity_id, bld_name, width * length, ...
>FROM Buildings
>SELECT 'ROADS', spatial_entity_id, road_name, NULL, ...
>FROM Roads
>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).

I think in practice what should be done is move all common entity attributes into separate tables: e.g. entity_name( entity_type, entity_id, entity_name); entity_location( entity_type, entity_id, lat, long, height), that keeps common stuff together and makes dealing with the whole set simple, without 1000 table unions that have to be modified every time a new entity type is added! With these data types, I think a bit of object modelling might help to determine the similarities/differences between the entities, before coming up with a physical database model; the logical database model stays the same with its 1000 entities.


Thanks. Take care, Brian Inglis 	Calgary, Alberta, Canada 	(Brian dot Inglis at SystematicSw dot ab dot ca)
    fake address		use address above to reply
						spam traps
Received on Thu May 16 2002 - 16:40:07 CEST

Original text of this message