Standard Database Setup (SDS)

The standard database setup started after I noticed many companies for whom I worked for had similar goals and most of them were doing it badly. I quit my jobs (gracefully) in April, 2000 because of the dot-com bust and because I didn't need a job, much to my joy. Joy? I had time to do what I always wanted to do. Many companies don't think long-term, and that annoys me because programming is long-term. I wanted to set a standard of database programming which can be used for all applications and to continue to improve it to make it more efficient and flexible. Basically, what we do here is setup s standard way of setting up tables and creating their stored procedures. I have broken down the levels of the database setup and also added a "plus" status which is independent of the level.

I welcome all people who wish to add to this and who wish to sign up as a member who supports this concept!

1. Definitions
2. Level One SDS
3. Level One SDS Plus
4. Level Two SDS
5. Level Two SDS Plus
6. Level Three SDS
7. Level Three SDS Plus

Definitions

• "TABLENAME" referes to the name of an arbitrary table.
• "FUNCTION" referes to an arbitrary function name for a table.
• Stored procedures will save its actions on a backup table described in the different levels of SDS.

  For stored procedures:

  1. "insert" shall recieve no input and return the primary key created.
  2. "update" shall update the data for the entire row of a table. The first input variable shall be the primary key. All subsequent variables will correspond to the exact order the of the fields in the table. 1 shall be returned for a positive update and a negative number for an error.
  3. "delete" shall recieve one input, the primary key which will be deleted. It will return 1 for positive result, or -1 or any negative number for any error. "delete" will not delete a row, but set its state to "inactive".
  4. "undelete" shall recieve one input, the primary key which will be undeleted. It will return 1 for positive result, or -1 or any negative number for any error. "undelete" will not restore a row, but set its state to "active".
  5. "purge" shall recieve no input. It will remove all deleted rows from the table (but keep a copy in the backup table as usual). It will return at least one value. The first value returned will be the number of rows affected (or or greater) or a negative result if there was an error. If the stored procedure can return a second value, it must return a space delimited text field which contains all the primary keys affected.
  6. "unpurge" shall recieve no input. It shall take the latest entry into the backup table for all rows deleted and restore them to the table. It will return at least one value. The first value returned will be the number of rows affected (or or greater) or a negative result if there was an error. If the stored procedure can return a second value, it must return a space delimited text field which contains all the primary keys affected.
  7. "purgeone" shall recieve one input, the primary key which will be purged. It will return 1 for positive result, or -1 or any negative number for any error. "purge" will remove only a deleted row from the table.
  8. "unpurgeone" shall recieve one input, the primary key which will be unpurged. It will return 1 for positive result, or -1 or any negative number for any error. "unpurge" will restore the latest "purged" row for that primarykey in the backtup table.
  9. "copy" shall copy the entire contents of a table into the same table but in a different row, except for the primary key and the timestamps. It shall return the primary key of the new row or a negative number to indicate an error.
  10. "change" shall only recieve three values, the primary key, the field desired to be changed, and the value to change it to. It shall return 1 for a positive result, or a negative number to indicate an error.

Level One SDS

Level One SDS Plus

Level Two

Everything in Level One plus the following. Does not have to include Level One Plus Items.