VbzCart/docs/archive/tables

From Woozle Writes Code
< VbzCart‎ | docs‎ | archive
Jump to navigation Jump to search

on other pages

updates

  • Purpose: timestamps on tables so we know when update queries need to be run
  • Status: probably superceded by data flow tables and procs

SQL

<mysql> CREATE TABLE `updates` (

   `Name` varchar(32) NOT NULL COMMENT 'name of thing which gets updated',
   `Updated` datetime COMMENT 'when last updated',
  PRIMARY KEY(`Name`)
) ENGINE = MYISAM;</mysql>

Function to record an update (not tested): <mysql> CREATE PROCEDURE SetUpdate(IN iName varchar(32))

BEGIN
  REPLACE INTO updates(Name,Updated) values(iName, NOW());
END</mysql>

MS Access version

These are notes on table design for vbz's order management system as implemented in Microsoft Access 97.

Titles

  • Last update: 2006-05-17
ID AutoNumber unique title identifier
Name Text full title
CatKey Text catalog key string (unique within department, unless department catkey is blank)
ID_Dept Number (long) Departments.ID - department for this item
ID_Licenser Number (long) Licensing agency supplying the image
DateAdded Date/Time date first added to catalog (NULL = unknown)
DateChecked Date/Time deprecated date availability status was last verified
DateUnavail Date/Time date title became no longer available (is this being used?)
RstkMin Number (long) minimum quantity which may be ordered in a restock (NULL = 1)
Supplier_CatNum Text supplier's catalog number for title
Supplier_CatNum_Alt Text deprecated supplier's alternate catalog number (e.g. old system) - now use alias tables instead
Desc Text descriptive text for title's web page & searches
Search Text additional words to use in searches, but not displayed
Notes Memo internal notes (not for display or searching)