Notes

/*
SQLyog Community v11.11 (64 bit)
MySQL - 5.6.24-log : Database - smirnoff
*********************************************************************
*/


/*!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 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`smirnoff` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `smirnoff`;

/*Table structure for table `trn_account` */

DROP TABLE IF EXISTS `trn_account`;

CREATE TABLE `trn_account` (
`account_id` INT(11) NOT NULL,
`account_name` VARCHAR(50) DEFAULT NULL,
PRIMARY KEY (`account_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

/*Table structure for table `trn_address` */

DROP TABLE IF EXISTS `trn_address`;

CREATE TABLE `trn_address` (
`address_id` INT(11) NOT NULL AUTO_INCREMENT,
`line1` VARCHAR(100) DEFAULT NULL,
`line2` VARCHAR(100) DEFAULT NULL,
`street` VARCHAR(100) DEFAULT NULL,
`city` VARCHAR(100) DEFAULT NULL,
`zip` VARCHAR(10) DEFAULT NULL,
`state` VARCHAR(100) DEFAULT NULL,
`country` VARCHAR(100) DEFAULT NULL,
PRIMARY KEY (`address_id`)
) ENGINE=INNODB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

/*Table structure for table `trn_customer` */

DROP TABLE IF EXISTS `trn_customer`;

CREATE TABLE `trn_customer` (
`pk_customer_map_id` INT(11) NOT NULL AUTO_INCREMENT,
`fk_account_id` INT(11) DEFAULT NULL,
`customer_number` VARCHAR(15) DEFAULT NULL,
`customer_name` VARCHAR(100) DEFAULT NULL,
`fk_customer_type` INT(11) DEFAULT NULL,
`fk_address_id` INT(11) DEFAULT NULL,
`company_code` VARCHAR(4) DEFAULT NULL,
PRIMARY KEY (`pk_customer_map_id`),
KEY `fk_customer_account_map_` (`fk_account_id`),
KEY `fk_customer_customer_type__map_` (`fk_customer_type`),
KEY `fk_customer_address_map_` (`fk_address_id`),
CONSTRAINT `fk_customer_account_map_` FOREIGN KEY (`fk_account_id`) REFERENCES `trn_account` (`account_id`),
CONSTRAINT `fk_customer_address_map_` FOREIGN KEY (`fk_address_id`) REFERENCES `trn_address` (`address_id`),
CONSTRAINT `fk_customer_customer_type__map_` FOREIGN KEY (`fk_customer_type`) REFERENCES `trn_customer_type` (`pk_customer_type_id`)
) ENGINE=INNODB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

/*Table structure for table `trn_customer_type` */

DROP TABLE IF EXISTS `trn_customer_type`;

CREATE TABLE `trn_customer_type` (
`pk_customer_type_id` INT(11) NOT NULL AUTO_INCREMENT,
`fk_account_id` INT(11) DEFAULT NULL,
`customer_type` VARCHAR(2) DEFAULT NULL,
`description` VARCHAR(100) DEFAULT NULL,
PRIMARY KEY (`pk_customer_type_id`),
KEY `fk_customer_type_account_map` (`fk_account_id`),
CONSTRAINT `fk_customer_type_account_map` FOREIGN KEY (`fk_account_id`) REFERENCES `trn_account` (`account_id`)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Table structure for table `trn_invoice` */

DROP TABLE IF EXISTS `trn_invoice`;

CREATE TABLE `trn_invoice` (
`pk_invoice_id` INT(11) NOT NULL AUTO_INCREMENT,
`fk_account_id` INT(11) DEFAULT NULL,
`invoice_number` VARCHAR(15) DEFAULT NULL,
`company_code` VARCHAR(4) DEFAULT NULL,
`fiscal_year` VARCHAR(4) DEFAULT NULL,
`item_number` VARCHAR(4) DEFAULT NULL,
`fk_invoice_type` INT(11) DEFAULT NULL,
`fk_posting_key` INT(11) DEFAULT NULL,
`fk_customer_id` INT(11) DEFAULT NULL,
`invoice_total_amount` DOUBLE(15,3) DEFAULT NULL,
`invoice_due_amount` DOUBLE(15,3) DEFAULT NULL,
`invoice_created_date` DATE DEFAULT NULL,
`due_date` DATE DEFAULT NULL,
`discount_1_percentage` INT(11) DEFAULT NULL,
`discount_2_percentage` INT(11) DEFAULT NULL,
`discount_3_percentage` INT(11) DEFAULT NULL,
`debit_credit_indicator` char(1) DEFAULT NULL,
`is_open` int(1) DEFAULT '1',
`approval_status` varchar(10) DEFAULT 'pending',
PRIMARY KEY (`pk_invoice_id`),
KEY `fk_invoice_account_map` (`fk_account_id`),
KEY `fk_invoice_invoice_type_map` (`fk_invoice_type`),
KEY `fk_invoice_posting_key_map` (`fk_posting_key`),
KEY `fk_invoice_customer_map` (`fk_customer_id`),
CONSTRAINT `fk_invoice_account_map` FOREIGN KEY (`fk_account_id`) REFERENCES `trn_account` (`account_id`),
CONSTRAINT `fk_invoice_customer_map` FOREIGN KEY (`fk_customer_id`) REFERENCES `trn_customer` (`pk_customer_map_id`),
CONSTRAINT `fk_invoice_invoice_type_map` FOREIGN KEY (`fk_invoice_type`) REFERENCES `trn_invoice_type` (`pk_invoice_type_map_id`),
CONSTRAINT `fk_invoice_posting_key_map` FOREIGN KEY (`fk_posting_key`) REFERENCES `trn_posting_key` (`pk_posting_key_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

/*Table structure for table `trn_invoice_type` */

DROP TABLE IF EXISTS `trn_invoice_type`;

CREATE TABLE `trn_invoice_type` (
`pk_invoice_type_map_id` int(11) NOT NULL AUTO_INCREMENT,
`fk_account_id` int(11) DEFAULT NULL,
`invoice_type` varchar(2) DEFAULT NULL,
`description` varchar(100) DEFAULT NULL,
PRIMARY KEY (`pk_invoice_type_map_id`),
KEY `fk_invoice_type_account_map` (`fk_account_id`),
CONSTRAINT `fk_invoice_type_account_map` FOREIGN KEY (`fk_account_id`) REFERENCES `trn_account` (`account_id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8;

/*Table structure for table `trn_posting_key` */

DROP TABLE IF EXISTS `trn_posting_key`;

CREATE TABLE `trn_posting_key` (
`pk_posting_key_id` int(11) NOT NULL AUTO_INCREMENT,
`fk_account_id` int(11) DEFAULT NULL,
`key_type` varchar(2) DEFAULT NULL,
`description` varchar(100) DEFAULT NULL,
PRIMARY KEY (`pk_posting_key_id`),
KEY `fk_posting_key_account_map` (`fk_account_id`),
CONSTRAINT `fk_posting_key_account_map` FOREIGN KEY (`fk_account_id`) REFERENCES `trn_account` (`account_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

/*!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 */;