I'm creating a data warehouse for a healthcare company. They have separate databases for different hospitals which contain tables on patients,their insurance,etc and PK is unique only within one hospital DB. When merged, I'm supposed to create a Master Patient Table, a Master Insurance Company table,etc that combines duplicate data into one record(eg. by comparing name and SSN fields for patients).
Any suggestions on how to do this merge, and create correct FK references in the new tables? A record in the Patient table needs to have a correct reference to an insurance company in the Insurance table. How do I preserve this?
I'll be using Oracle for both the source and destination databases.