Database Schema

General Notes
Currently there are 3 tables clients - assets - associations.

There are currently no constraints built into the SQL tables - but this will change in the future. Currently all constraints are built into the software.

association.assetID aligns to asset.assetNumber and association.clientID aligns to client.clientNumber

Asset
CREATE TABLE `asset` (   `assetNumber` varchar(30) NOT NULL,    `type` varchar(30) NOT NULL,    `model` varchar(30) NOT NULL,    `modelType` varchar(30) NOT NULL,    `serialNo` varchar(30) NOT NULL,    `assetTag` varchar(30) NOT NULL,    `TSOTag` varchar(30) NOT NULL,    `status` varchar(45) NOT NULL,    `physicalLocation` varchar(30) NOT NULL,    `year` varchar(30) NOT NULL,    `purchasedBy` varchar(30) NOT NULL,    `wifiMAC` varchar(30) NOT NULL,    `ethernetMAC` varchar(20) NOT NULL,    `description` varchar(45) NOT NULL,    PRIMARY KEY (`assetNumber`)  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Association
CREATE TABLE `association` (  `assocKey` int(11) NOT NULL AUTO_INCREMENT,  `assetID` varchar(30) NOT NULL,  `clientID` varchar(30) NOT NULL,  `assocType` varchar(30) NOT NULL,  `dateCreated` varchar(30) NOT NULL,  `comment` longtext NOT NULL,  PRIMARY KEY (`assetID`,`clientID`),  UNIQUE KEY `assocKey_UNIQUE` (`assocKey`) ) ENGINE=InnoDB AUTO_INCREMENT=579 DEFAULT CHARSET=latin1;

Client
CREATE TABLE `client` (  `clientNumber` varchar(30) NOT NULL,  `firstName` varchar(30) NOT NULL,  `lastName` varchar(30) NOT NULL,  `type` varchar(30) NOT NULL,  `cohort` int(11) NOT NULL,  `gender` varchar(20) NOT NULL,  `DOB` varchar(30) NOT NULL,  `comments` longtext NOT NULL,  `emailAddress` varchar(65) DEFAULT NULL,  PRIMARY KEY (`clientNumber`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Maintenance
CREATE TABLE `maintenance` (  `idmaintenance` int(11) NOT NULL AUTO_INCREMENT,  `status` varchar(45) DEFAULT NULL,  `assetNumber` varchar(45) NOT NULL,  `dateCreated` datetime NOT NULL,  `dateClosed` datetime NOT NULL,  `incNumber` varchar(45) DEFAULT NULL,  `courierNumber` varchar(45) DEFAULT NULL,  `vendorTicketNumber` varchar(45) DEFAULT NULL,  `shortProblemDescription` mediumtext,  `problemDescription` longtext,  `resolutionNotes` longtext,  `callLog` longtext,  PRIMARY KEY (`idmaintenance`) ) ENGINE=InnoDB AUTO_INCREMENT=67 DEFAULT CHARSET=latin1; SELECT * FROM perm.maintenance;