Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

For testing purposes we have a sanitized copy of our production database in our test world. Because of the way it is sanitized, we now have empty fields where encrypted data previously existed. We would like to be able to run against the database and have dummy data appear to be in the empty sanitized fields.

My initial thought is to have views with the same name as the sanitized tables that would return dummy (probably fixed) data in the empty fields. However, my understanding is that a View cannot exist with the same name as a Table. Is it possible to work around this?

My first thought was to use schemas, but I don't know enough about schemas at the moment. My second thought is to have a second "wrapper" database that references objects in the first database.

Is there a common practice for handling this sort of task?

share|improve this question
4  
Wouldn't it be just as easy to update the empty columns with dummy data as to create a view that generates the required dummy data? –  Martin Smith Dec 31 '13 at 18:26
1  
Or, depending on how it's encrypted and who is managing the keys, simple not have the keys present on the test server. –  Edward Dortland Dec 31 '13 at 19:18
    
You can also rename the table first, then make your View with the table's original name, pulling it's data from the renamed table. But I'd probably do what @MartinSmith said and just update the table. –  RBarryYoung Jan 1 at 17:23

1 Answer 1

You could use an updatable view as a proxy in front of the base table behind it. The view should behave the same way the table did, with some exceptions (for example, TRUNCATE will not work).

The table can the have any name because it is never referenced by app code.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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