I've been dealing with data migration out of ancient AS400 piece by piece through linked server connection and I'm having hard time figuring out the best way to structure everything into more or less decent way.
I'm joining multiple tables into views to get a piece of data I'm looking for and then creating tables out of that views. I have tables that have information about different parties like "attorney, commissioner, claimant(id, names, contact info etc) those tables also contain addresses of corresponding party. What
I would like to do is to have only one Address table and collect all of the addresses in there but I'm having hard time keeping up with referential integrity since each tbl has over 1,5 million records.
What would be the right way do achieve that?
Here are example columns in some tbls:
Claimant(Id,ClaimNo, First/LastName, Phone, email, Street, City, State, Zip)
Attorney(AttCode, First/LastName, FirmId, Phone, email, Street, City, State, Zip)
Insurer(InsCode, Name, PolicyNo, Phone, email, Street, City, State, Zip) etc