[postgis-users] Database Design for many tables

[Paul Ramsey]

Are schemas not useful here?

<http://www.postgresql.org/docs/8.1/interactive/ddl-schemas.html>

On 5-Jan-07, at 4:24 PM, Dylan Beaudette 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.
>>> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users