Re: Managing two sets of data in one database

Tom -

Your suggestion gives me an idea, and I'd like your opinion since I haven't
done much with schemas.

(1) Separate the datasets into different schemas
(2) Use different schema names for different static data releases
(3) For the *company*, we can use a schema search path that includes the
next schema
(4) For the *users*, they can use a schema search path that includes the
released schema

Then, I wouldn't have to modify any code, except to set the schema search
path based on who was connecting.

Does this sound reasonable?

Jon

On Thu, Mar 29, 2012 at 1:26 PM, Tom Molesworth <tom(at)audioboundary(dot)com>wrote:

> Hi Jonathan,
>
>
> On 29/03/12 19:01, Jonathan Bartlett wrote:
>
>
>
>> Now, my issue is that right now when we do updates to the dataset, we
>>> have to make them to the live database. I would prefer to manage data
>>> releases the way we manage software releases - have a staging area, test
>>> the data, and then deploy it to the users. However, I am not sure the best
>>> approach for this. If there weren't lots of crossover queries, I could
>>> just shove them in separate databases, and then swap out dataset #1 when we
>>> have a new release.
>>>
>>>
>> you can't JOIN data across relations(tables) in different databases.
>>
>>
> Right. That's the reason I asked on the list. I didn't know if there
> is a good way of managing this sort of data. If I could just have two
> different databases, I would have done that a while ago. I didn't know if
> someone had a similar situation and what kind of solution they used for it.
> Right now, both datasets are in the same database. But that means I can't
> do releases of the static dataset, and instead, when the company updates
> the database, we have to make the updates directly on the live database.
> I'm trying to avoid that and do releases, and I am seeing if anyone knows
> of a good approach given the constraints.
>
>
> Have you considered using views in the queries instead of hitting the base
> tables directly? You could then load the releases into a different schema
> (so instead of select * from mytable, you have a view which does select *
> from release_20110329.mytable, for example) or use different table names
> for each release (live_*, test_*, beta_* maybe). Switching between releases
> should be fast (and atomic), but everything would still be within the same
> database so you'd be able to get to all the data you need.
>
> cheers,
>
> Tom
>
>