In most of the database sytem we use to come across a common master table…i.e. Customer master…

Customer Master

  1. Customer id
  2. First Name of Customer
  3. Last Name of Customer
  4. Address Line 1
  5. Address Line 2
  6. City
  7. District
  8. State
  9. Country
  10. PIN (Postal Index used in India)

How we normally define the customer master?….We can give an example with a preassumption that this is a database of a business who have presence across different states of INDIA.Now these Customer master has some attributes ( 6,7,8) which if not properly used can simply turn the whole database into a garbage. Like say for District KOLKATA (former Calcutta) user can enter “Kolkata” or ” Kolkata” or “Kolkata.” . So for any customer related MIS reports will wrongly show three different district instead of a single district. To tackle this situation database designer introduce some other masters like City Master, District Master, State Master and even Country Master. Whose structure Could be almost as the following….

    (1)                (2)                   (3)              (4)

City Master    District Master State Master Customer master
City Code        District Code     State Code     City Code   
City Name       District Name     State Name    State Code
District Code   State Code                             District code (Only changed fied)

This is something pretty good design for the normal scenario. But the Political Incidents happened in recent past ….Division of state and districts…intruduced new folds on the forehead of the database developers. Let us consider a city “c1” which intially belonged to district “d1” and state “s1” is attached to district “d2” and state “s2”. This happens in the middle of finacial year and person from DSS Level is looking at a district wise matrix report for the region. Financial and legal components added to transanction will change acording to this change which ultimately will depict stiff upward or downward change in the district sales curve of the company.

Normally post dated annoncement happens for such changes, any big organisation takes sometime to finish update their database, and finally when evrything is updated history reports comes wrong…report executed after updation of database (the city “c1” is in district “d2” and state “s2”) for the period of before updation will come wrong. How to Solve this issue?