Re: Help with complex db design concepts
Date: Thu, 16 May 2002 14:40:07 GMT
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, ...
>SELECT 'ROADS', spatial_entity_id, road_name, NULL, ...
>SELECT 'BODY_OF_WATER', spatial_entity_id, name, area, ...
>/* 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
>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 trapsReceived on Thu May 16 2002 - 16:40:07 CEST