[postgis-users] Database Design for many tables

[Dylan Beaudette]

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.
> > At our institute we are planning to set up a database where all
> > spatial and non spatial data should be held central on a server. We
> > have two different working groups. One works mainly on water projects
> > the other in the cleaner production context.
> > We are planning to setup an intranet interface with QGIS and GRASS in
> > order for the knowledge and needs of the user to work with the data.
> > For data entries by users we want to use pgadmin and phppagadmin. As
> > internet and plublic interface we want to use Mapserver. For
> > documentation a metadatasystem is planned after the ISO19115 where we
> > filter out our needed core elements for the data. Additionally we will
> > implement a search function for keywords to filter out data quite
> > rapidly.
> > For the future we probably get a lot of tables (count > 200) mixed up
> > in different topics and types (spatial and non-spatial).
> > What do you think? Should we create databases for any topic and try to
> > categorise the tables or mix it all together in one database and end
> > up in mess. I think administration in pgadmin suffers with that amount
> > of tables but is always better than on the commandline with psql.
> > Thank you for your comments,
> >
> > Christian
>
> Hi Christian,
>
> I have had success in the past by using a single database but by
> categorising the tables into different schemas by function/project. I
> don't see anything wrong with mixing spatial/non-spatial tables within a
> single schema as the database engine sees them all as the same anyway...
>
>
> Kind regards,
>
> Mark.
>
>

Hi everyone,

I am jumping into this thread not because I have an answer, but rather a 
similar set of questions:

I too have a postgis setup accessed by a number of people. A little 
organization of tables would be a great logistic help. Unfortunately cross-DB 
queries are not possible with postgres, so I have been investigating the use 
of tablespaces. However, I have not found much documentation or advice on the 
implementation of postgis functionality for multiple tablespaces. This is 
especially important when multiple projects, and datasets share common table 
names. 

Is there a simple way to achieve some sort of organization (as if I had each 
project in a separate DB), and still retain postgis _and_ cross-project query 
functionality?

Any ideas would be a great help!

Cheers,


-- 
Dylan Beaudette
Soils and Biogeochemistry Graduate Group
University of California at Davis
530.754.7341