Problem of analysis in governmental offices!

Posted on 2004-10-22
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
    LVL 12

    Expert Comment

    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.

    LVL 2

    Accepted Solution

    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


    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Lean Six Sigma Project Manager Certification

    There are many schools of thought around successful project management, but few as highly regarded as the Six Sigma and Lean methods. With 37 hours of learning, this training will explain concrete processes for increasing efficiency and limiting wasted time and effort.

    Suggested Solutions

    Purpose To explain how to place a textual stamp on a PDF document.  This is commonly referred to as an annotation, or possibly a watermark, but a watermark is generally different in that it is somewhat translucent.  Watermark’s may be text or graph…
    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
    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…

    877 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now