Re: Help with complex db design concepts

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.
>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).

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.Inglis_at_CSi.com 	(Brian dot Inglis at SystematicSw dot ab dot ca)
    fake address		use address above to reply

tosspam_at_aol.com abuse_at_aol.com abuse_at_yahoo.com abuse_at_hotmail.com abuse_at_msn.com abuse_at_sprint.com abuse_at_earthlink.com abuse_at_cadvision.com abuse_at_ibsystems.com uce_at_ftc.gov
						spam traps