Daan O. arrived at work only to find all of his peers sitting in the conference room, looking scared; very scared! The salesman had snagged a new client by promising that all of their data from their current application could be trivially imported into Daan's company's primary product - no problem-o! Unfortunately, the salesman had not bothered to ask the new customer what the data looked like. Or in what format it existed. Or how much of it was there. Or if it was in any way consistent. He had just made the promise. Now it was their problem to make it all work. Unfortunately for Daan, he had been victimized assigned with the task of making the magic happen.

First, the data was delivered as a plethora of Excel spreadsheets, each one representing a table in the client database. Perhaps job-one should be to dump it all into scratch tables and try to work from there.

After some casual perusal, Daan discovered that every record had two unique keys: recordId and [Name Of The Table]Id. Ok, it could be worse. Then he discovered the crown jewel of the database; the Child table: 

    Column Name         Comment
    dateOfBirth         timestamp in the form of ms
    monthOfBirth        full name of the month of birth
    age                 varchar2(64): contains things like: "The child is now 16 months old"
    nursery1            name
    nursery2            name
    nursery3            name
    nursery4            name
    addressNursery1     address
    addressNursery2     address
    addressNursery3     address
    addressNursery4     address
    contractYesNo       e.g.: "Not yet", "Not made", "Signed+returned", "FILE_NOT_FOUND"

Daan couldn't tell which was worse; what horror would be required to keep the age field current, what would happen if a 5th nursery was required, why the apparently unrelated Nursery table even existed, or what purpose the duplicate unique keys served. But then he hit the mother-load in the Child table:

    Column Name         Sample Data
    gender              "Boy", "girl"
    heshe               "he",  "she"
    himher              "him", "her"
    hisher              "his", "her"
    sondaughter         "son", "daughter"
[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!