Standard Database Setup (SDS) version 1.0. 
Copyright by Mark Nielsen, 9/2001. 

1. All tables must contain the following:
   a. A primary key equal to TABLENAME_id.
   b. Timestamps for the date of creation and modification, date_created
      and date_updated. 
   c. There will be a backup table named TABLENAME_backup. 
   d. All fields are lower case.
   e. An active field whose status of 0 is inactive and 1 is active. 
   f. All foreign keys must have the extension "_fk" except for fieldnames
      ending in "_id".
   g. All fieldnames with "_id" in the table definition will automatically
      get a foreign keys constraint. A fieldname of "TABLENAME_id" will
      have a foreign constraint to the table "TABLENAME" with its primary key
      "TABLENAME_id". 
   h. Views for active, inactive (deleted), and purged will be created. 
   i. Insert, Update, Delete, Copy, Change, Purge, Unpurge, PurgeOne, 
       and UnpurgeOne functions are too be used for all database 
       modifications where the functions will be 
       named "TABLENAME_FUNCTION_sql".     
   j. When stored procedures can return multiple variables, then all
      select statements will be executed through stored procedures as well. 
      All select stored procedures will end in "_select_sql".
      The two types of select stored procedures will be:
      1. Given a unique id number, the select statement will require
         all fields for that row. This stored procedures will be named
         "TABLENAME_select_sql". 
      2. All other select statements will be custom made. Select statements
         can get very complicated. It is mandatory that all custom select
         statements be created with custom stored procedures. 
   k. All modifications to any table will be recorded in "TABLENAME_backup".
      Modifications will occur through standard stored procedures and
      the stored procedures will handle recording of these modifications. 
      "backup_id" will the primary key of all backup tables.  
   l. All selects of tables will be recorded as an option using the table
      "TABLENAME_select" if it is desired when the table is created. 
      "TABLENAME_select" will have the fields select_id, date_created, 
      date_updated, TABLENAME_id, error_code, and misc.   
   m. An optional method for recording database inserts and changes
      with respect to differences may be used, but must follow the following
      standards. This will not be included by default in MAPPS. 
      1. The backup table must be named TABLENAME_diff.  
      2. "diff_id" will be the primary key for each table. 
      3. Each diff table will have exactly these fields.
         a. diff_id 
         b. date_updated : Timestamp the data was entered. 
         c. diff_data : This is the difference in data between this
            entry and the previous entry. 
         d. diff_method : This describes the difference method used.
             This is arbitrary and dependent upon the programmer. Here are
             a list of pre-defined methods:
            1. "diff" or "gnudiff" will correspond to GNU diff. The version
               must be supplied and recorded somewhere. 
            2. "subversion" in relation to subversion. 
            3. cvs will not be an option for a standard. "subversion" replaces
               cvs, and hence, by the time SDS is really solid, subversion
               from subversion.org will be ready. 
         e. diff_prev : This is the previous diff_id which the data is 
            being compared to. This is done so that if a diff_id gets
            deleted, you can state there is an error. 
         f. fieldname : This is the field of the row which the data
            belongs to.  
         g. primary : This is the primary key of the row we are looking at. 
         h. error_code : "start" means there is no diff_id previous
            to this one. "diff" means there is a previous entry to
            be compared to. "stop" means the primary key has been deleted. 
            Other error codes are possible. 
   n. An entry of 0 into a foreign key means null. All rows with a primary
      key of 0 should have empty space for text and 0 for numeric values
      or the default value (if supplied). 

2. The unique names which cannot be used are:
   a. "error_code", "backup_id", "date_created", "date_updated",
      , "diff_id" and "active" are reserved fields. 

3. Standard practices with Perl Modules and other programming languages. 
   a. All Perl Modules (or other programming languages) will create
      objects whose methods correspond to each stored procedure. The naming
      convention of each method shall be exactly the sql function name minus
      the tablename and "_sql". Thus, "TABLENAME_FUNCTION_sql" becomes
      "FUNCTION" in the Perl Module. There will be an exact one to one
      correspondence between all sql functions and perl methods, minus
      custom made sql functions or perl methods. 
    b. All custom made perl methods must use stored procedures for all
       changing of data, and selecting data (when stored procedures
       can return multiple variables). 
     
4. Standard practices of custom stored procedures.
   a. All custom stored procedures may only change data using one of the
      predefined stored procedures. Custom stored procedures may not
      use custom made stored procedures for changing/inserting data. 
   b. All select statements must use select stored procedures (when the
      stored procedures can return multiple values). 

5. Standard practices of webpages/perl scripts.
   a. All perl scripts or other programming languages will always
      use perl modules for all interactivity with database. All changing,
      inserting, or viewing data will occur through a module.