importing sub tables into exisiting database
I have a database file that includes a table for people and linked subtables for work history and education. People has a unique field - PID - generated serial number; Ed and Hist records are linked to PID; new Ed and Hist records are created through a portal on a People layout.
information on People is being collected from different sources; one such file is similarly structured as above. I can sort out the the main table fields, but am not clear on how best to handle the linked records. In importing the people I did think to add a field for and keep the orginal UniqueID, so incoming related records might get connected, but then I want to clear up the redundancies.
In briefest form, the critical bits:
Target database has Table=People including it's own <PID> unique field, and <ImportID>, from the source records.
and Table=History, with a <PID_fk> for each job record (with its own ui)
Source database has Table=People including unique field <ImportID>
and Table=History with an <ImportID_fk> for each job record (with it's own ui)
I think my question is What is best way to get ImportID lined up with and then replaced by the existing PID. On the theory I don't want two keys for the same purpose in a join.
i considered reusing the Imported ID as the PID, but that seems wrong. not only does it disturb my sense of order to have different serial id series, I am guessing it won't work to just rerun the numbering later once all the data is in. <s>.
I could envision importing as a new table and then doing a look up / import from one table to the next (how)? but that seems a long way round. So throwing it out there for thoughts on processes.