Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Problem of analysis in governmental offices!

Posted on 2004-10-22
Medium Priority
Last Modified: 2010-04-17
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?
Question by:Rosa2003
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 11

Expert Comment

ID: 12382875
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.


Accepted Solution

skeeveswp earned 1200 total points
ID: 12383333
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

Assisted Solution

ariel_t earned 800 total points
ID: 12383665

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


Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Make the most of your online learning experience.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question