Postgresql – Organizing Database Objects in a Database that are not part of the systems original Database Objects

best practicespostgresql

I've been working at a place for about 2 years now with a series of heterogeneous legacy database applications, which are tied together using 3rd party Microsoft Access databases (*.mdb). Soon we will switch them to Postgres in a new integrated system.

The 3rd party *.mdbs worked well for separating out what queries (VIEWs) and which temporary tables belonged to which process for performing a task of some sort; since they were separated into their own files along with the VBA code.

The new system will be similar to the old systems but instead have them integrated together, and there will be less need for such 3rd party databases for the time being; However, should a new need arise, and a new VIEW or temporary table be required in the Postgres database; What is a best practice for grouping a series of VIEWs or temporary tables together so that later it can be known what task/purpose they belong to?

I would think that it would be beneficial to have some sort of a library structure for the VIEWs / temporary tables such that ones used for many different tasks are separated into:

  • common (used for many different tasks)
  • task1Views (specific to task1)
  • task2Views (specific to task2)

I'm trying to recreate the functionality in Access where a list of queries (VIEWs) and tables related to the task at hand are listed on the left side and are searchable only for that task. And also to prevent confusion among tables that already exist for the system (linked tables in Access) and tables that I am using to perform a task.

Best Answer

On one hand, a sensible naming convention can solve part of your problem.

However, a more sophisticated solution is to use schemas: they are a good way to organize your stuff and also make you able to set access rights at this level.

For example, we usually create a data schema (named after the covered functionality and suffixed with '_data') and one or more API schemas. Tables, the types used for their columns, triggers along with their procedures, sequences and so on all go into the data schema. As applications are allowed to call API functions only (no arbitrary SQL is allowed), it just makes sense to put all of them into a separate schema, this is what I refer as API schema above. There may be further ones (utility schema, versioning schema and so on), too.

Of course, your needs may be different, but I hope this illustrates well how schemas can be used.

In your case it is probably better to create schemas mapping the functional grouping. Then you issue a

CREATE SCHEMA task1_views;

command, and create the objects either with specifying the schema, like

CREATE OR REPLACE VIEW task1_views.important_view ...

or set the search_path first, if you create many objects in a row:

SET search_path TO task1_views;
CREATE OR REPLACE VIEW important_view ... -- no schema specified
Related Question