Problem of analysis in governmental offices!

Hello Experts!

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):
NAME        VARCHAR(25)

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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi Rosa2003,

I think without knowinf the use of the app it is almost impossible to come up with new ideas.
According to your statement 1) you shouldn't deal yourself with new IDs anyhow.
That leaves only modifying records. Modyfied records have to be merged at the time of an update. The rules for the merge are not known however.

I think that using a temporary ID table could be a viable answer.

The process would be to enter the new ID into the temporary table and process it normally, but flag every transaction as coming from an unverified ID.  Then, when you receive your quarterly reports, you can cross-reference between the temp table and the incoming data and validate all transactions according to the official listing.

If any trasactions do not validate against the quarterly report, then those transactions can be printed for investigation.  You will have to gauge the impact of invalid transactions against the inconvenience of potentially having to wait for three monts to process a valid transaction.

I would consider this to be a temporary solution while the agreement with office2 is implemented.  I don't know what kind of transactions you handle, but there is a relatively high probability of invalid transactions when you can't verify the ID's.

The only other option I can see is to initiate the process to make the agreement with office2 and live with the existing difficulties until it is ratified.

-- Steve

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

What you have there is a typical Slowly Changing Dimension (SCD), and third way is an appropriate solution for this anywhere, if you want to save the history of your clients' activities. It's not complex at all, you must add 2 fields to you table - "Valid" and "Date", where "Valid" is true or false and "Date" is a date when the record turned to invalid.

It's not so clear if you can recieve "table of changes" daily or not , if not, you must get web service anyway

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.