Difference between revisions of "VbzCart/archive/tables"
Jump to navigation
Jump to search
(ctg_updates now discarded) |
(moved archival page from VbzWiki) |
||
| Line 5: | Line 5: | ||
* '''Status''': probably superceded by data flow tables and procs | * '''Status''': probably superceded by data flow tables and procs | ||
===SQL=== | ===SQL=== | ||
| − | + | <mysql> CREATE TABLE `updates` ( | |
`Name` varchar(32) NOT NULL COMMENT 'name of thing which gets updated', | `Name` varchar(32) NOT NULL COMMENT 'name of thing which gets updated', | ||
`Updated` datetime COMMENT 'when last updated', | `Updated` datetime COMMENT 'when last updated', | ||
PRIMARY KEY(`Name`) | PRIMARY KEY(`Name`) | ||
) ENGINE = MYISAM;</mysql> | ) ENGINE = MYISAM;</mysql> | ||
| − | |||
Function to record an update (not tested): | Function to record an update (not tested): | ||
| − | + | <mysql> CREATE PROCEDURE SetUpdate(IN iName varchar(32)) | |
BEGIN | BEGIN | ||
REPLACE INTO updates(Name,Updated) values(iName, NOW()); | REPLACE INTO updates(Name,Updated) values(iName, NOW()); | ||
END</mysql> | 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) | ||
| + | |} | ||
Revision as of 13:59, 22 December 2012
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) |