We've got a problem of analysis.
We work for Official_Office_1 and are developing a web system for them. Citizens of my country will use that web system.
The web system uses TBL_PEOPLE table, where 18 million records are storaged. It's a table of citizens of the country.
desc TBL_PEOPLE (basically fields):
We receive that Tbl_People from Official_Office_2 (they own and manage that table) every 3 months. So, we don't have records that have been modified or added in those 3 months (in 3 months a lot of people can die, or change their names, or new citizens can be added to that TBL_PEOPLE)
The problem for us will be when some people will use the web system and won't find their CITIZEN_ID, because we haven't received yet the new TBL_PEOPLE from Official_Office_2
What can we do in this case?
What we can do:
1.- Insert the Citizen_ID (that doesn't appear) into TBL_PEOPLE. But that is a problem. Actually we must not do that, because Official_Office_2 is in charge to do that and they know how to do it correctly.
2.- Use a web-service from Official_Office_2 and then get Citizen_ID, Name and LastName. But to do that, the two offices have to sign an official agreenment between them and that could take a long time because bureaucracy.
3. Insert the new or modified Citizen_Id records into another table like TBL_PEOPLE, which will contain only those new or modified records. But our programming will get very complex, don't you think so?
What would you recommend us, please?