mysql - Segregating sandbox environment -

for site using sandbox mode, such payment site, separate database used, or same one?

i examining 2 schemas production , sandbox environment. here 2 options.

option 1:

• clone database, route requests correct database based upon sandbox mode.

option 2

• single database, 'main tables' have is_sandbox boolean.

what pros , cons of each method?

in situations, you'd want keep 2 separate databases. there's no reason have 2 intermingled in same database, , lot of reasons keep them separated:

• keeping track of entities in "realm" (production vs. sandbox) work code, , you'll have include in lot of places.

• you'll need logic in database schema well. unique indexes have include realm, instance.

• if forget of code, you've got potential security vulnerability. malicious user cause data 1 realm influence other. depending on application is, range anywhere annoying terrifying. (if it's payment application, instance, potential consequences incredibly dire: "pretend" money sandbox converted real money!)

• even if code perfect, there'll still information unavoidably leaked between realms. instance, if application uses sequential identifiers (auto_increment in mysql, instance), gaps in values seen in sandbox correspond values used in production. whether matters debatable, though.

using 2 separate databases neatly solves these problems. means can clean out sandbox when needed.

exception: if application entirely public web site (e.g, stack overflow or wikipedia), or involves social aspects difficult replicate in sandbox (like facebook), more integrated sandboxes may make more sense.