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 <t...@audioboundary.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 > >