sd-spreadsheet/schema.sql

/*
SQLyog Ultimate v12.14 (64 bit)
MySQL - 5.7.11 : Database - sdpickup
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*Table structure for table `company` */

DROP TABLE IF EXISTS `company`;

CREATE TABLE `company` (
  `id_company` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `ftp_directory` varchar(32) DEFAULT NULL COMMENT 'name of subdirectory for ftp scanning',
  `name` varchar(64) NOT NULL,
  `description` text,
  PRIMARY KEY (`id_company`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

/*Table structure for table `files_processed` */

DROP TABLE IF EXISTS `files_processed`;

CREATE TABLE `files_processed` (
  `id_file_processed` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_company` smallint(5) unsigned NOT NULL,
  `filename` varchar(64) DEFAULT NULL,
  `size` bigint(20) unsigned DEFAULT NULL,
  `num_rows` int(10) unsigned DEFAULT NULL,
  `num_failed` int(10) unsigned DEFAULT NULL,
  `date_processed` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_file_processed`),
  KEY `id_company` (`id_company`),
  CONSTRAINT `files_processed_ibfk_1` FOREIGN KEY (`id_company`) REFERENCES `company` (`id_company`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `records` */

DROP TABLE IF EXISTS `records`;

CREATE TABLE `records` (
  `id_record` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `id_company` smallint(6) unsigned NOT NULL,
  `division` varchar(64) DEFAULT NULL COMMENT 'Dynamic Delivery Services',
  `consignee` varchar(32) DEFAULT NULL COMMENT 'GW-PVH',
  `date_called_in` date DEFAULT NULL COMMENT '05/04/2016',
  `dispatch_number` varchar(32) DEFAULT NULL COMMENT '22940402-001',
  `shipper` varchar(128) DEFAULT NULL COMMENT 'Joe Shipping Firm',
  `date_ready` date DEFAULT NULL COMMENT '05/04/2016',
  `address_1` varchar(128) DEFAULT NULL COMMENT '123 4th St',
  `address_2` varchar(128) DEFAULT NULL COMMENT 'Apt 1',
  `city` varchar(128) DEFAULT NULL COMMENT 'Beverly Hills',
  `state` char(2) DEFAULT NULL COMMENT 'CA',
  `zip` varchar(32) DEFAULT NULL COMMENT '90210',
  `phone` varchar(32) DEFAULT NULL COMMENT '2015551234',
  `time_ready` time DEFAULT NULL COMMENT '08:00 AM',
  `time_close` time DEFAULT NULL COMMENT '05:00 PM',
  `time_pickup` time DEFAULT NULL,
  `time_depart` time DEFAULT NULL,
  `bol_delivered` varchar(64) DEFAULT NULL,
  `dispatch_reference` varchar(32) DEFAULT NULL,
  `ctns` int(11) DEFAULT NULL COMMENT '20',
  `weight` decimal(10,0) DEFAULT NULL COMMENT '500.00',
  `cube` decimal(10,0) DEFAULT NULL COMMENT '42.00',
  PRIMARY KEY (`id_record`),
  KEY `records_ibfk_1` (`id_company`),
  CONSTRAINT `records_ibfk_1` FOREIGN KEY (`id_company`) REFERENCES `company` (`id_company`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=162 DEFAULT CHARSET=latin1;

/*Table structure for table `update` */

DROP TABLE IF EXISTS `update`;

CREATE TABLE `update` (
  `id_record` bigint(10) unsigned NOT NULL,
  `id_user` int(10) unsigned NOT NULL,
  `key` varchar(32) DEFAULT NULL,
  `value` varchar(64) DEFAULT NULL,
  `date_modified` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  KEY `id_user` (`id_user`),
  KEY `id_record` (`id_record`),
  CONSTRAINT `update_ibfk_1` FOREIGN KEY (`id_user`) REFERENCES `user` (`id_user`) ON UPDATE CASCADE,
  CONSTRAINT `update_ibfk_2` FOREIGN KEY (`id_record`) REFERENCES `records` (`id_record`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `user` */

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id_user` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_company` smallint(5) unsigned NOT NULL,
  `email` varchar(64) NOT NULL,
  `password` varchar(128) DEFAULT NULL,
  `date_created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_user`),
  UNIQUE KEY `email` (`email`),
  KEY `id_user` (`id_user`),
  KEY `id_company` (`id_company`),
  CONSTRAINT `user_ibfk_1` FOREIGN KEY (`id_company`) REFERENCES `company` (`id_company`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;