[postgis-users] Database Design for many tables

[Webb Sprague]

On 1/5/07, Dylan Beaudette <dylan.beaudette at gmail.com> wrote:
> On Friday 05 January 2007 01:07, Mark Cave-Ayland wrote:
> > On Fri, 2007-01-05 at 09:53 +0100, Christian Braun wrote:
> > > Hi all,
> > >
> > > I have a question regarding the database design of a PostGIS database.

It might be really interesting if either of you could either direct us
to entity relation models of your data or post prototype schemas or
some other design documentation (any attempt would be better than
nothing); then we would have something to work with.

With respect to soils, I would imagine that you would create a table
for every classification that partitions the space under question,
giving each polygon in a table a unique id. If the soils data is
county by county shapefiles, you probably want to append it to a
single table, as long as it is the "same thing".

For non geographic data, typical normalization etc etc stuff applies,
but you will be able to bring in geographic information through joins
on the various primary keys.

If you can keep everything in one big database and have people
remotely access it (not a problem usually) that would be easiest.  If
performance suffers, have a master and slave set up, pushing out
updates after they are QA'ed, otherwise you will have a nightmare of
version control.

If you have people editing shared data, lets talk :)  ...  Give us
your SPECIFIC use cases and we can probably help (or at least sound
intelligent).

If you are using rasters, I have no idea how to integrate postgis with
them, but you should mention it to the list or in your rough design
docs.

If you don't know what an entity relationship model is, or what
database normalization is, you probably need to read up on database
design, but that would be worthwhile anyway.  Start with wikipedia, as
usual.

W