Supply Chain Management Database (SCheMatic: The Supply Chain Manager-O-Matic)
------------------------------------------------------------------------------

TABLES:

Vendor
VID (P-key) | VLID | signedNDA | Name
CREATE TABLE Vendor (vid INT NOT NULL PRIMARY KEY AUTO_INCREMENT, vlid INT, signedNDA TINYINT(1) NOT NULL DEFAULT 0, name VARCHAR(100), FOREIGN KEY (vlid) REFERENCES VendorLevel(vlid)) ENGINE=INNODB;

VendorLevel
VLID (P-key) | VendorUsable | Color | Description
CREATE TABLE VendorLevel (vlid INT NOT NULL PRIMARY KEY AUTO_INCREMENT, vendorUsable TINYINT(1) NOT NULL DEFAULT 0, color INT(4), description VARCHAR(100)) ENGINE=INNODB;

VendorType
VTID (P-key) | VGID | SeqNo | Description
CREATE TABLE VendorType (vtid INT NOT NULL PRIMARY KEY AUTO_INCREMENT, vgid INT, seqNo INT(4) NOT NULL, description VARCHAR(100), FOREIGN KEY (vgid) REFERENCES VendorGroup(vgid)) ENGINE=INNODB;

VendorGroup
VGID (P-key) | SeqNo | Description
CREATE TABLE VendorGroup (vgid INT NOT NULL PRIMARY KEY AUTO_INCREMENT, seqNo INT(4) NOT NULL, description VARCHAR(100)) ENGINE=INNODB;

VendorSpecificTypes
VID (multi) | VTID (multi)
CREATE TABLE VendorSpecificTypes (vid INT, vtid INT, FOREIGN KEY (vid) REFERENCES Vendor(vid), FOREIGN KEY (vtid) REFERENCES VendorType(vtid)) ENGINE=INNODB;

Location
LID (P-key) | VID | Address | City | State | Country | Code
CREATE TABLE Location (lid INT NOT NULL PRIMARY KEY AUTO_INCREMENT, vid INT, address VARCHAR(100), city VARCHAR(64), state VARCHAR(64), country VARCHAR(64), code VARCHAR(32), FOREIGN KEY (vid) REFERENCES Vendor(vid)) ENGINE=INNODB;

Contact
CID (P-key) | VID | CTID | Name | Email | Address | Phone1 | Phone2 | Fax
CREATE TABLE Contact (cid INT NOT NULL PRIMARY KEY AUTO_INCREMENT, vid INT, CTID int, name VARCHAR(64), email VARCHAR(64), address VARCHAR(200), phone1 VARCHAR(32), phone2 VARCHAR(32), fax VARCHAR(32), FOREIGN KEY (vid) REFERENCES Vendor(vid), FOREIGN KEY (ctid) REFERENCES ContactType(ctid)) ENGINE=INNODB;

ContactType
CTID (P-key) | Description
CREATE TABLE ContactType (ctid INT NOT NULL PRIMARY KEY AUTO_INCREMENT, description VARCHAR(100)) ENGINE=INNODB;

User
UID (P-key) | UCID | Name | Login | Password
CREATE TABLE User (uid INT NOT NULL PRIMARY KEY AUTO_INCREMENT, ucid int, name VARCHAR(64), login VARCHAR(32), password VARCHAR(64), FOREIGN KEY (ucid) REFERENCES UserClass(ucid)) ENGINE=INNODB;

UserClass
UCID | PrivilegeLevel | Description
CREATE TABLE UserClass (ucid INT NOT NULL PRIMARY KEY AUTO_INCREMENT, privilegeLevel INT NOT NULL DEFAULT 0, description VARCHAR(100)) ENGINE=INNODB;

PurchaseOrder
POID (P-key) | VID | Closed | PONumber | OriginalDate | AdjustedDate | Description
CREATE TABLE PurchaseOrder (poid INT NOT NULL PRIMARY KEY AUTO_INCREMENT, vid INT, closed TINYINT(1) NOT NULL DEFAULT 0, poNumber VARCHAR(100), originalDate DATE, adjustedDate DATE, description VARCHAR(200), FOREIGN KEY (vid) REFERENCES Vendor(vid)) ENGINE=INNODB;

Documents
DID (P-key) | DTID | VID | POID | Filename | Document
CREATE TABLE Documents (did INT NOT NULL PRIMARY KEY AUTO_INCREMENT, dtid INT, vid INT, poid INT, filename VARCHAR(256), document LONGBLOB, FOREIGN KEY (dtid) REFERENCES DocumentType(dtid)) ENGINE=INNODB;

DocumentType
DTID (P-key) | Description
CREATE TABLE DocumentType (dtid INT NOT NULL PRIMARY KEY AUTO_INCREMENT, description VARCHAR(100)) ENGINE=INNODB;

Notes
NID (P-key) | UID | POID | Note
CREATE TABLE Notes (nid INT NOT NULL PRIMARY KEY AUTO_INCREMENT, uid INT, poid INT, note VARCHAR(200)) ENGINE=INNODB;

Alerts
AID (P-key) | UID | NID | Date | Description
CREATE TABLE Alerts (aid INT NOT NULL PRIMARY KEY AUTO_INCREMENT, uid INT, nid INT, date DATE, description VARCHAR(200), FOREIGN KEY (uid) REFERENCES User(uid)) ENGINE=INNODB;

History
UID | Date | Note
CREATE TABLE History (uid INT, date DATETIME, note VARCHAR(100), FOREIGN KEY (uid) REFERENCES User(uid)) ENGINE=INNODB;

-------------------------------------------------------------------------------

PurchaseRequest
PRID (P-key) | UIDRequestor | Description
CREATE TABLE () ENGINE=INNODB;

PRItems
PRID (multi) | Quantity | UnitPrice | Description
CREATE TABLE () ENGINE=INNODB;

PurchaseApprovals
PRID (multi) | UIDApprover | Approved
CREATE TABLE () ENGINE=INNODB;

Approvers
UIDApprover (multi) | UIDRequestor (multi)
CREATE TABLE () ENGINE=INNODB;