[postgis-users] Database Design for many tables

[Mark Cave-Ayland]

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.