Re: Managing two sets of data in one database

On 03/29/12 9:43 AM, Jonathan Bartlett wrote:
> 1) A large (~150GB) dataset. This data set is mainly static. It is
> updated, but not by the users (it is updated by our company, which
> provides the data to users). There are some deletions, but it is safe
> to consider this an "add-only" database, where only new records are
> created.
> 2) A small (~10MB but growing) dataset. This is the user's data. It
> includes many bookmarks (i.e. foreign keys) into data set #1.
> However, I am not explicitly using any referential integrity system.

by 'dataset' do you mean table, aka relation ?

by 'not using any referential integrity', do you mean, you're NOT using
foreign keys ('REFERENCES table(field)' in your table declaration ?

>
> Also, many queries cross the datasets together.
>

by 'cross', do you mean JOIN ?

> 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.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast