I like to create a tool which scrambles the production sensitive data and added it into another dev DB. I've built a couple of scripts that make random social security numbers, shift birth dates, scramble emails, etc. But I faced a problem while we have a reference for that column.

For example:- if we scramble tables tableA and TableB,
TableB have a forieng key from tableA(ssn)

    ssn firstName                  lastName
    111   arun                        prasanth                 
    222   ann                         antony                   
    333   sruthy                      abc                      
    666   new                         abc     

TableB(ssn Foreign key reference)
id  ssn   Place
1   111   kerala
2   222   usa
3   333   ekm
5   666   chennai

Desired output should be

    ssn firstName                  lastName
    999   gdfsgs                    trttr
    956   hsggghhgh                 pipip
    855   hdsgfgsddd                lklkl
    233   ghfgfggfgf                mkmk

TableB(ssn Foreign key reference)
id  ssn   Place
1   999   kerala
2   956   usa
3   855   ekm
5   233   chennai

Since your development environment evidently enforces foreign key relationships, changing the value of a key is a problem.

Options include:

Designing the database so that an important fact like SSN isn't an internal key in the first place (too late to do that).

(If SSN in your database doesn't represent an important fact, then it may not be necessary to change it in the development copy, since it only exposes meaningful data when connected to the real personal data in production, which you are removing. But you and your colleagues may disagree with me.)

Changing data for one SSN at a time - for instance input a row of 000, Proxy, Proxy in TableA; change all SSN of 111 in other tables to 000; change SSN 111 in TableA to 999; change all SSN 000 in other tables to 999. I have done this in production data, when someone was recorded with two different service IDs in different databases and we needed to merge together the data from two IDs.

Copying data to a database that does not have referential constraints on the tables; change the data however you like, but finishing with correct relationships; copy the data again into another database where there are constraints.

If the language and design allows, delete or turn off the foreign key enforcement, change your data, then create or turn on the foreign keys. But beware of constraints in the database that were already disabled. If at the end you turn on all constraints that were disabled, you may also turn on the constraints that were already disabled before you started.

Standard tools to produce anonymous identity data may be useful to you. For names, I've used a method of taking a list of reasonable and distinct forenames, and a list of surnames, two different prime numbers in length (or "co-prime"). Take names in (semi random) order from one list, and from the other, in turn, returning from end of list to the start. Combinations of names shouldn't be repeated until all pairs have been used once. My list included one spelling of "Mohamed", and as a surname, because I thought it would be provocative to produce a "Mohamed Singh". It also didn't occur to me to use "hsggghhgh", but you could use "User101first" and "User101last", etc.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.