CODEWITHSUNDEEP
sqldatabase
Casey Dwayne
Casey Dwayne

Reputation: 2169

Temporary Tables Quick Guide

I have a structured database and software to handle it and I wanted to setup a demo version based off of a simple template version. I'm reading through some resources on temporary tables but I have questions.

What is the best way to go about cloning a "temporary" database while keeping a clean list of databases?

From what I've seen, there are two ways to do this - temporary local versions that are terminated at the end of the session, and tables that are stored in the database until deleted by the client or me.

I think I would prefer the 2nd option, because I would like to be able to see what they do with it. However, I do not want add a ton of throw-away databases and clutter my system.

How can I a) schedule these for deletion after say 30 days and b) if possible, keep these all under one umbrella, or in other words, is there a way to keep them out of my main list of databases and grouped by themselves.

I've thought about having one database and then serving up the information by using a unique ID for the user and 'faux indexes' so that it appears as 1,2,3 instead of 556,557,558 to solve B. I'm unsure how I could solve A, other than adding a date and protected columns and having a script that runs daily and deletes if over 30 days and not protected.

I apologize for the open-ended question, but the resources I've found are a bit ambiguous.

Upvotes: 0

Views: 51

Answers (1)

alroc
alroc

Reputation: 28174

These aren't true temp tables in the sense that your DBMS knows them. What you're looking for is a way to have a demo copy of your database, probably with a cut-down data set. It's really no different from having any other non-production copy of your database.

  1. Don't do this on your production database server.
  2. Do not do this on your production database server.
  3. Script the creation of your database schema. Depending on the DBMS you're using, this may be pretty easy. If you've got a good development/deployment/maintenance process for your system, this should already exist.
  4. Create your database on the non-production server using the script(s) generated in the previous step. Use an easily-identifiable naming convention, like starting the database name with demo.
  5. Load any data required into the tables.
  6. Point the demo version of your app (that's running on your non-production servers) at this new database.
  7. Create a script/process/job which looks at your database server and drops any databases that match your demo DB naming convention and were created more than 30 days ago.

Without details about your actual environment, people can't give concrete examples/sample code/instructions.

If you cannot run a second, independent database server for these demos, then you will have to make do with your production server. This is still a bad idea because of potential security exposures and performance impact on your production database (constrained resources).

  • Create a complete copy of your database (or at least the schema, with a reduced data set) for each demo.
  • Create a unique set of credentials for each of these demo databases. This account should have access to only its demo database.
  • Configure the demo instance(s) of your application to connect to the demo database

Here's why I'm pushing so hard for separate databases: If you keep copying your "demo" tables within the database, you will have to update your application code to point at those tables each time you do a new demo. Once you start doing this, you're taking a big risk with your demos - the code you keep changing isn't really the application you're running in production anymore. And if you miss one of those changes, you'll get unexpected results at best, and mangling of your production data at worst.

Upvotes: 2

Related Questions