Dear all
I'd really appreciate a little advice here - I'm designing a PG
database to manage a scientific dataset.
I've these fairly clear requirements:
1. Multiple users of varying skill will input data.
2. Newly inserted data will be audited and marked good / bad
3. We must have a dataset that is frozen or "known good" to feed into
various models.
This, as far as I can see, leaves me with three options:
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.
C. Sandbox tables for all tables updated by the application.
I prefer option A, this gives me the flexibility to run heavy
modelling queries on a separate server, but I'm not sure how best to
deal with the replication issues when moving to the modelling db.
Option B makes me think of hard to diagnose bugs with queries looking
at different datasets, for example.
With option C, if both tables tX and tY have sandbox tables sX and sY,
there could be problems where sX needs to reference data in sY, but
has a foreign key referencing tY
What would you guys do? Have I missed a better option here?
Thanks
Will T