データベースとテーブルの準備

データベース名

データベース名はsalesdbとします。

テーブル定義

テーブルは以下の6つです。

顧客 - customers
商品 - products
在庫 - stocks
伝票 - invoices
伝票明細 - invoice_items
日計 - daily_summaries

ER図 (関連は示していません)

サンプルアプリケーション用データベースのER図

-------------------------
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
-------------------------

テーブル作成

以下のSQL文を実行すると上記6つのテーブルを作成できます。事前に作成しておきましょう。もちろんTransactd APIでも作成できますが、ここでの本題ではないので省きます。

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;