Search code examples
mysqldatabase-designsandbox

Segregating sandbox environment

For a site that is using a Sandbox mode, such as a Payment site, would a separate database be used, or the same one?

I am examining two schemas for the production and sandbox environment. Here are the two options.

OPTION 1:

  • Clone database, route requests to the correct database based upon sandbox mode.

OPTION 2

  • Single database, 'main tables' have an is_sandbox boolean.

What would be the pros and cons of each method?

Solution

  • In most situations, you'd want to keep two separate databases. There's no good reason to have the two intermingled in the same database, and a lot of very good reasons to keep them separated:

    • Keeping track of which entities are in which "realm" (production vs. sandbox) is extra work for your code, and you'll likely have to include it in a lot of places.

    • You'll need that logic in your database schema as well. UNIQUE indexes all have to include the realm, for instance.

    • If you forget any of that code, you've got yourself a potential security vulnerability. A malicious user could cause data from one realm to influence the other. Depending on what your application is, this could range anywhere from annoying to terrifying. (If it's a payment application, for instance, the potential consequences are incredibly dire: "pretend" money from the sandbox could be converted into real money!)

    • Even if your code is all perfect, there'll still be some information unavoidably leaked between the realms. For instance, if your application uses any sequential identifiers (AUTO_INCREMENT in MySQL, for instance), gaps in values seen in the sandbox will correspond with values used in production. Whether this matters is debatable, though.

    Using two separate databases neatly solves all these problems. It also means you can easily clean out the sandbox when needed.

    Exception: If your application is an almost entirely public web site (e.g, like Stack Overflow or Wikipedia), or involves social aspects that are difficult to replicate in a sandbox (like Facebook), more integrated sandboxes may make more sense.