Re: Help with complex db design concepts

From: Paul G. Brown <paul_geoffrey_brown_at_yahoo.com>
Date: 9 May 2002 12:55:26 -0700
Message-ID: <57da7b56.0205091155.1f655f13_at_posting.google.com>

zaq42_at_yahoo.com (zaq) wrote in message news:<185bc2bb.0205081416.bd80df4_at_posting.google.com>...
> I have a problem I've not face in my ten years of database design. I
> have an idea for the solution, but really want feedback.
>
> 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 ]  

> I know this is difficult, but I'm trying to find a innovative way to
> glue this enterprise-level applciation together.

  Why are you splitting the spatial objects up into so many tables? They're   all just 'features' of the geographic landscape, and they can be   labelled to reflect their type. What about the following schema?

  CREATE TABLE Geo_Features (

      Id           System_Generated_Identity     PRIMARY KEY,
      What         Feature_Type                  NOT NULL,
      Where        ST_Spatial_Type               NOT NULL

  );

  CREATE TABLE Documents (

      Id           System_Generated_Identity     PRIMARY KEY,
      What         Document                      NOT NULL

  );

  CREATE TABLE Resolution (

      Geo_Feature  INTEGER REFERENCES Geo_Features ( Id ),
      Document     INTEGER REFERENCES Documents ( Id )
           PRIMARY KEY ( Geo_Feature, Doocument )

  );

  Each of your 1000 layers can be implemented as a view. Note that   the possibility exists that some users want to create their own layers   which combine multiple primititive feature layers (ROADS + RIVERS + RAILS ).

    ( Forgive the mangled syntax here: I'm just getting to grips with the    ANSI SQL).    CREATE VIEW Roads AS
   SELECT G.What, G.Where

          ,D.Documentation 
     FROM Geo_Features G 
          OUTER JOIN Resolution R 
                     INNER JOIN Documents D
                     ON R.Document = D.Id 
          ON G.Id       = R.Geo_Feature 

   WHERE G.What IN ('ROAD', 'RAIL', 'RIVERS' ); -- For example.

    Should work an absolute treat. Let your users define their VIEWs at   run-time. Note that you'll probably need decent spatial indexing on the   G.What to get reasonable performance.

    Hope this helps. The spatial part of the problem is a bit of a red herring.  What you have here is exactly the same problem as a parts catalog, where a  great many kinds of parts (or products) need to be related to a number of  customers (or orders). And to an RDBMS, that's bread and butter.

    KR

           Pb Received on Thu May 09 2002 - 21:55:26 CEST