Re: Managing two sets of data in one database

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