Create database and tables
Database name
The database name is salesdb
.
Table definitions
The tables are following six:
customers
products
stocks
invoices
invoice_items
daily_summaries
ER diagram (without relations)
-------------------------
customers table
int : id
string : name
timestamp : update_at
primarykey : id
-------------------------
products table
int : id
string : code
string : description
int : price
string : note
timestamp : update_at
primarykey : id
index : code
-------------------------
stocks table
string : code
int : quantity
timestamp : update_at
primarykey : code
-------------------------
invoices table
int : id
date : date
int : customer_id
int : sales_amount
int : tax_amount
int : payment_amount
int : balance_amount
string : note
timestamp : update_at
primarykey : id
index : customer_id, date, id
index : date, id (duplicatable)
-------------------------
invoice_items table
int : invoice_id
int : row
int : line_type
string : product_code
string : product_description
int : price
int : quantity
int : amount
int : tax
string : note
timestamp : update_at
primarykey : invoice_id, row
-------------------------
daily_summaries table
date : date
int : slaes_amount
int : tax_amount
int : payment_amount
primarykey : date
-------------------------
Create tables
The following SQL will create these six tables. Create them beforehand. Of course you can create them with the Transactd API, but it is not a main subject, so I omit it.
CREATE DATABASE salesdb;
USE salesdb;
CREATE TABLE `customers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`update_at` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `products` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`description` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`price` int(11) NOT NULL,
`note` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`update_at` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
PRIMARY KEY (`id`),
UNIQUE KEY `key1` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `stocks` (
`code` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`quantity` int(11) NOT NULL,
`update_at` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `invoices` (
`id` int(11) NOT NULL,
`date` date NOT NULL,
`customer_id` int(11) NOT NULL,
`sales_amount` int(11) NOT NULL,
`tax_amount` int(11) NOT NULL,
`payment_amount` int(11) NOT NULL,
`balance_amount` int(11) NOT NULL,
`note` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`update_at` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
PRIMARY KEY (`id`),
UNIQUE KEY `key1` (`customer_id`,`date`,`id`),
KEY `key2` (`date`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `invoice_items` (
`invoice_id` int(11) NOT NULL,
`row` int(11) NOT NULL,
`line_type` tinyint(4) NOT NULL,
`product_code` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`product_description` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`price` int(11) NOT NULL,
`quantity` int(11) NOT NULL,
`amount` int(11) NOT NULL,
`tax` int(11) NOT NULL,
`note` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`update_at` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
PRIMARY KEY (`invoice_id`,`row`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `daily_summaries` (
`date` date NOT NULL,
`slaes_amount` int(11) NOT NULL,
`tax_amount` int(11) NOT NULL,
`payment_amount` int(11) NOT NULL,
PRIMARY KEY (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;