Re: Help with complex db design concepts

"Paul G. Brown" wrote:
>
> 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

I vote for this kind of approach. Many of the Geographical Features will have common attributes that can be located in the Geo_Features table. Even those attributes only applicable to a subset of Geographical Features can be located there with null values for the types they don't apply to (salinity or PH levels for bodies of water but null for telephone poles and buildings). You can add one-to-one tables for other attributes too specific for the Geo_Features table (starting elevation, ending elevation, etc.). But you wouldn't need 1000 of them. It's a judgment call based on requirements.

The other advantage of this approach is it's easier to relate the Geographical Features to each other. You could have a watershed where you wanted to know what rivers, streams, lakes, factories, roads and bridges were part of it. And don't tell me the number of Geographical Features is fixed, wait until your company buys another company and you find out they are have an interest in moon craters. Some new features wouldn't even require any intervention from you, just an insert of a new feature type by the customer.

I love views, but wouldn't want to maintain hundreds of them. Again, it depends on the requirements. A view with a join among Resolution, Documents and Geo_Features would meet most of your needs for linking documents to features. On the other hand, a thousand tables guarantees you a job for life. Life of the system anyway.

Doug S. Received on Fri May 10 2002 - 19:01:09 CEST