Re: Design decision advice

From: "William Temperley" <willtemperley(at)gmail(dot)com>
To: "Craig Ringer" <craig(at)postnewspapers(dot)com(dot)au>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Design decision advice
Date: 2008-08-14 09:44:47
Message-ID: 439dc11e0808140244m50570837o49a8823732c2e850@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Thu, Aug 14, 2008 at 2:55 AM, Craig Ringer
<craig(at)postnewspapers(dot)com(dot)au> wrote:
> William Temperley wrote:

>> A. Two databases, one for transaction processing and one for
>> modelling. At arbitrary intervals (days/weeks/months) all "good" data
>> will be moved to the modelling database.
>> B. One database, where all records will either be marked "in" or
>> "out". The application layer has to exclude all data that is out.
>
> You could also exclude "out" data at the database level with appropriate
> use of (possibly updatable) views.
>
> If you put your raw tables in one schema and put your valid-data-only
> query views in another schema, you can set your schema search path so
> applications cannot see the raw tables containing not-yet-validated data.
>
> You also have the option of using materialized views, where a trigger
> maintains the "good" tables by pushing data over from the raw tables
> when it's approved.
>
> That gives you something between your options "A" and "B" to consider,
> at least.
>
> --
> Craig Ringer
>
>

Thanks Craig -

I didn't know about the search_path setting - a gem of knowlege. I'd
overlooked views too.

I'm using Django btw, which is great except for limited support for
multiple DBs, so the single DB option will be much easier.

Search_path gives me quite an elegant solution - I can direct my
read-only modelling users to their schema ($user), the modelling
schema, where views are kept.
Admin users get directed to their $user schema.

This leaves me with the views/materialised views question.
Oh yeah, and hacking Django to allow different DB users in one project.

Cheers,

Will

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Clemens Schwaighofer 2008-08-14 09:52:35 Postgres 8.3 is not using indexes
Previous Message Tom Tom 2008-08-14 09:44:33 Re: pg_restore fails on Windows