--
-- Host: localhost    Database: schematic
-- ------------------------------------------------------


--
-- Table structure for table `Alerts`
--

CREATE TABLE `Alerts` (
  `aid` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) DEFAULT NULL,
  `nid` int(11) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `description` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`aid`),
  KEY `uid` (`uid`),
  CONSTRAINT `Alerts_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `User` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `Alerts`
--


--
-- Table structure for table `Contact`
--

CREATE TABLE `Contact` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `vid` int(11) DEFAULT NULL,
  `CTID` int(11) DEFAULT NULL,
  `name` varchar(64) DEFAULT NULL,
  `email` varchar(64) DEFAULT NULL,
  `address` varchar(200) DEFAULT NULL,
  `phone1` varchar(32) DEFAULT NULL,
  `phone2` varchar(32) DEFAULT NULL,
  `fax` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`cid`),
  KEY `vid` (`vid`),
  KEY `CTID` (`CTID`),
  CONSTRAINT `Contact_ibfk_1` FOREIGN KEY (`vid`) REFERENCES `Vendor` (`vid`),
  CONSTRAINT `Contact_ibfk_2` FOREIGN KEY (`CTID`) REFERENCES `ContactType` (`ctid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `Contact`
--


--
-- Table structure for table `ContactType`
--

CREATE TABLE `ContactType` (
  `ctid` int(11) NOT NULL AUTO_INCREMENT,
  `description` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`ctid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `ContactType`
--


--
-- Table structure for table `DocumentType`
--

CREATE TABLE `DocumentType` (
  `dtid` int(11) NOT NULL AUTO_INCREMENT,
  `description` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`dtid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `DocumentType`
--


--
-- Table structure for table `Documents`
--

CREATE TABLE `Documents` (
  `did` int(11) NOT NULL AUTO_INCREMENT,
  `dtid` int(11) DEFAULT NULL,
  `vid` int(11) DEFAULT NULL,
  `poid` int(11) DEFAULT NULL,
  `filename` varchar(256) DEFAULT NULL,
  `document` longblob,
  PRIMARY KEY (`did`),
  KEY `dtid` (`dtid`),
  KEY `vid` (`vid`),
  KEY `poid` (`poid`),
  CONSTRAINT `Documents_ibfk_1` FOREIGN KEY (`dtid`) REFERENCES `DocumentType` (`dtid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `Documents`
--


--
-- Table structure for table `History`
--

CREATE TABLE `History` (
  `uid` int(11) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `note` varchar(100) DEFAULT NULL,
  KEY `uid` (`uid`),
  CONSTRAINT `History_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `User` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `History`
--


--
-- Table structure for table `Location`
--

CREATE TABLE `Location` (
  `lid` int(11) NOT NULL AUTO_INCREMENT,
  `vid` int(11) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `city` varchar(64) DEFAULT NULL,
  `state` varchar(64) DEFAULT NULL,
  `country` varchar(64) DEFAULT NULL,
  `code` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`lid`),
  KEY `vid` (`vid`),
  CONSTRAINT `Location_ibfk_1` FOREIGN KEY (`vid`) REFERENCES `Vendor` (`vid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `Location`
--


--
-- Table structure for table `Notes`
--

CREATE TABLE `Notes` (
  `nid` int(11) NOT NULL AUTO_INCREMENT,
  `poid` int(11) DEFAULT NULL,
  `note` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`nid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `Notes`
--


--
-- Table structure for table `PurchaseOrder`
--

CREATE TABLE `PurchaseOrder` (
  `poid` int(11) NOT NULL AUTO_INCREMENT,
  `vid` int(11) DEFAULT NULL,
  `closed` tinyint(1) NOT NULL DEFAULT '0',
  `poNumber` varchar(100) DEFAULT NULL,
  `originalDate` date DEFAULT NULL,
  `adjustedDate` date DEFAULT NULL,
  `description` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`poid`),
  KEY `vid` (`vid`),
  CONSTRAINT `PurchaseOrder_ibfk_1` FOREIGN KEY (`vid`) REFERENCES `Vendor` (`vid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `PurchaseOrder`
--


--
-- Table structure for table `UserClass`
--

CREATE TABLE `UserClass` (
  `ucid` int(11) NOT NULL AUTO_INCREMENT,
  `privilegeLevel` int(11) NOT NULL DEFAULT '0',
  `description` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`ucid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `UserClass`
--

INSERT INTO `UserClass` VALUES (1,1,'Administrator');

--
-- Table structure for table `Vendor`
--

CREATE TABLE `Vendor` (
  `vid` int(11) NOT NULL AUTO_INCREMENT,
  `vlid` int(11) DEFAULT NULL,
  `signedNDA` tinyint(1) NOT NULL DEFAULT '0',
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`vid`),
  KEY `vlid` (`vlid`),
  CONSTRAINT `Vendor_ibfk_1` FOREIGN KEY (`vlid`) REFERENCES `VendorLevel` (`vlid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `Vendor`
--


--
-- Table structure for table `VendorGroup`
--

CREATE TABLE `VendorGroup` (
  `vgid` int(11) NOT NULL AUTO_INCREMENT,
  `seqNo` int(4) NOT NULL,
  `description` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`vgid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `VendorGroup`
--


--
-- Table structure for table `VendorLevel`
--

CREATE TABLE `VendorLevel` (
  `vlid` int(11) NOT NULL AUTO_INCREMENT,
  `vendorUsable` tinyint(1) NOT NULL DEFAULT '0',
  `color` int(4) DEFAULT NULL,
  `description` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`vlid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `VendorLevel`
--

INSERT INTO `VendorLevel` VALUES (1,1,65280,'1;Trusted');
INSERT INTO `VendorLevel` VALUES (2,1,255,'2;Caution Advised');
INSERT INTO `VendorLevel` VALUES (3,0,16719904,'X;DO NOT USE');

--
-- Table structure for table `VendorSpecificTypes`
--

CREATE TABLE `VendorSpecificTypes` (
  `vid` int(11) DEFAULT NULL,
  `vtid` int(11) DEFAULT NULL,
  KEY `vid` (`vid`),
  KEY `vtid` (`vtid`),
  CONSTRAINT `VendorSpecificTypes_ibfk_1` FOREIGN KEY (`vid`) REFERENCES `Vendor` (`vid`),
  CONSTRAINT `VendorSpecificTypes_ibfk_2` FOREIGN KEY (`vtid`) REFERENCES `VendorType` (`vtid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `VendorSpecificTypes`
--


--
-- Table structure for table `VendorType`
--

CREATE TABLE `VendorType` (
  `vtid` int(11) NOT NULL AUTO_INCREMENT,
  `vgid` int(11) DEFAULT NULL,
  `seqNo` int(4) NOT NULL,
  `description` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`vtid`),
  KEY `vgid` (`vgid`),
  CONSTRAINT `VendorType_ibfk_1` FOREIGN KEY (`vgid`) REFERENCES `VendorGroup` (`vgid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `VendorType`
--



