July 26, 2012 at 10:16 am
Couple of questions to better understand the scenario...
1- Why two databases e.g. intake and portal? when it appears like one would do the trick?
2- Intake will act as a source for reporting, is that correct?
3- Does Intake database data has to be near-real-time updated or can it be updated as it was - let's say at 5AM?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 26, 2012 at 11:26 am
Hi Paul,
Thanks for the reply.
1. I was thinking that since the portal was read only, and intake would need the ability to add/update data, that a separate db's might be best. But if this is not the case, then I guess I could go with a single database. Actually it might make it simpler if I only use one database.
2. Intake dept will be entering in assessments on clients to be admitted to one of the programs (we are drug treatment clinic). Once the assessment is saved, data from this info will be used to create a new client record (if brand new client) and also creating a new admission record.
At first I was just going to have all the assessments saved in the Intakedb only, but after thinking a little more about this, maybe its better to have Assessment table in each program database, and when intake saves the assessment, just have the new data replicated back to the appropriate program database. You think that is a better design?
3. The intake database need to be near real time, where as the portal I might be able to get away with updates every couple of hours, and maybe even once a day. The user of the portal will not be employees of the company, but will be state workers that will need limited access to the data.
Hope this help clarify what I'm trying to.
Michael
July 26, 2012 at 11:48 am
Hi Paul,
I thought it also may help a little bit to give a little more detail as to how we are setup now:
Note, database structure is the same for all Programs. Each program has there own database.
Site A (Queens)
Program 1
Program 2
Intake dept for Program 1 & 2
Site B (Brooklyn)
Program 3
Program 4
Intake dept for Program 3 & 4
Site C (Bronx)
Program 5
Program 6
Program 7
Program 8
Intake dept for Programs 5,6,7 and 8
Hope this helps.
Michael
July 26, 2012 at 12:36 pm
Let me test my understanding of this...
a) Portal is a read-only subset of tables from each database
b) Intake will support an OLTP system
c) Phisical locations appear to be pretty close to each other e.g. Bronx, Queens and Brooklyn.
IF Intake is expected to work by "program" meaning that every single Intake transaction targets a specific "program" - which equates to a particular database in a particular location - I may consider:
1) Use the current "program" databases to support the Intake application and let the Intake application point to the right one.
2) Build a Portal database to support read-only reporting. This database could source data replicated from the "program" databases.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply