Re: Design decision advice

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