X-Git-Url: http://shamusworld.gotdns.org/cgi-bin/gitweb.cgi?a=blobdiff_plain;f=database-schema.txt;h=3b5a34bafb70a1671cf2d6f6cd77f2d35f746cbb;hb=f3116511d09acfd5b32d3412c82c4337d89f2ad9;hp=a7bc7415db41bee9bfdf0b86d17920b2eb96bf2a;hpb=6c9ba10f64c4880fd4c58527c462ef1f7841bf17;p=schematic

diff --git a/database-schema.txt b/database-schema.txt
index a7bc741..3b5a34b 100644
--- a/database-schema.txt
+++ b/database-schema.txt
@@ -4,28 +4,28 @@ Supply Chain Management Database (SCheMatic: The Supply Chain Manager-O-Matic)
 TABLES:
 
 Vendor
-VID (P-key) | VLID | Name
-CREATE TABLE Vendor (vid INT NOT NULL PRIMARY KEY AUTO_INCREMENT, vlid INT, name VARCHAR(100), FOREIGN KEY (vlid) REFERENCES VendorLevel(vlid)) ENGINE=INNODB;
+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) | VendorUseable | 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 | Description
-CREATE TABLE VendorType (vtid INT NOT NULL PRIMARY KEY AUTO_INCREMENT, vgid INT, description VARCHAR(100), FOREIGN KEY (vgid) REFERENCES VendorGroup(vgid)) ENGINE=INNODB;
+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) | Description
-CREATE TABLE VendorGroup (vgid INT NOT NULL PRIMARY KEY AUTO_INCREMENT, description VARCHAR(100)) ENGINE=INNODB;
+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 | Description
-CREATE TABLE Location () ENGINE=INNODB;
+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
@@ -44,12 +44,12 @@ 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 | Description
-CREATE TABLE PurchaseOrder (poid INT NOT NULL PRIMARY KEY AUTO_INCREMENT, vid INT, closed TINYINT(1) NOT NULL DEFAULT 0, description VARCHAR(200), FOREIGN KEY (vid) REFERENCES Vendor(vid)) ENGINE=INNODB;
+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 | Document
-CREATE TABLE Documents (did INT NOT NULL PRIMARY KEY AUTO_INCREMENT, dtid INT, vid INT, poid INT, document LONGBLOB, FOREIGN KEY (dtid) REFERENCES DocumentType(dtid), FOREIGN KEY (vid) REFERENCES Vendor(vid), FOREIGN KEY (poid) REFERENCES PurchaseOrder(poid)) ENGINE=INNODB;
+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