VbzCart/docs/archive/tables: Difference between revisions
Jump to navigation
Jump to search
imported>Woozle (ctg_updates now discarded) |
imported>Woozle (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) |