C言語＋MySQL入門｜在庫履歴テーブルを作成する

C言語＋MySQL入門｜在庫履歴テーブルを作成する

前の記事では、商品の基本情報と現在の在庫数を保存するproductsを作成しました。今回は、商品の入出庫を記録する在庫履歴テーブル、stock_historyを作成します。

現在の在庫数が分かっていても、それだけでは、いつ、何個入庫したのか、どのように在庫が減ったのかまでは分かりません。在庫の変化を履歴として残しておくと、現在の数量に至る経緯を確認できます。

この記事では、次の内容を学びます。

• 商品テーブルと在庫履歴テーブルの役割を区別する。
• 1回の入出庫を1行として記録する設計を理解する。
• 入出庫の種別と数量の表し方を決める。
• CREATE TABLEでstock_historyを作成する。
• 作成した列や制約を確認する。

まずは、履歴を保存するためのテーブルを用意しましょう。商品テーブルとの外部キーによる関連付けは、次の記事で行います。

現在の在庫数と在庫履歴を分けて考えよう

productsのstockには、現在の在庫数を保存します。

たとえば、ある商品のstockが25であれば、その商品の在庫が25個あることを表します。ただし、この値だけでは、最初から25個あったのか、入出庫を繰り返して25個になったのかは分かりません。

そこで、在庫が変わるたびに、その内容をstock_historyへ記録します。

たとえば、在庫0個の商品について、次のような操作があったとします。

productsには現在の在庫数25を保存し、stock_historyには3回分の入出庫を3行で保存する、という関係です。

ここで示している数値は、仕組みを理解するための例です。この記事の操作で、これらの商品や履歴を登録するわけではありません。

図1：現在の在庫数と入出庫の履歴

この図から分かること

現在の在庫数と在庫履歴は、同じ商品について異なる情報を表しています。

productsを見ると今の数量が分かり、stock_historyを見ると数量が変化した経緯を確認できます。現在の値だけでなく、その値に至る記録も残すことが、在庫管理では大切です。

在庫履歴に必要な5つの列を決めよう

stock_historyには、次の5つの列を用意します。

5つの列は、すべてNOT NULLとします。対象商品や数量が分からない履歴は、今回のアプリでは扱わない設計です。

history_idとproduct_idは別の番号

history_idは、履歴を1件ずつ識別するための番号です。

一方、product_idは、その履歴がどの商品についての記録なのかを示します。

同じ商品でも、入出庫が3回あれば履歴は3件になります。そのため、stock_historyのproduct_idには、同じ値が繰り返し登場します。

次の表は、保存するデータのイメージです。

3件とも対象商品は101ですが、history_idはそれぞれ異なります。

商品を区別する番号と、履歴を区別する番号を分けることが、このテーブルの重要なポイントです。

product_idをstock_historyの主キーにすると、同じ商品の履歴を複数保存できなくなってしまいます。今回はhistory_idを主キーにします。

入出庫種別と数量を組み合わせる

movement_typeには、入庫か出庫かを表す文字列を保存します。

quantityには、どちらの場合も正の整数を保存します。

たとえば、5個出庫するときは、movement_typeをOUT、quantityを5とします。quantityに負の数を入れる設計にはしません。

これにより、役割が次のように分かれます。

• movement_typeは、増やすか減らすかを表す。
• quantityは、何個動かしたかを表す。

なお、表にある増減の操作は、今後作成するアプリが行う処理です。INやOUTを保存するだけで、MySQLがproductsの在庫数を自動的に変更するわけではありません。

作成先のデータベースを確認しよう

MySQLクライアントへ管理作業用のアカウントで接続し、mysql>が表示されている状態で進めます。

まず、inventory_dbを選択します。

USE inventory_db;

実行結果の例

Database changed

続けて、既存のテーブルを確認しましょう。

SHOW TABLES;

実行結果の例

+------------------------+
| Tables_in_inventory_db |
+------------------------+
| products               |
+------------------------+
1 row in set (0.00 sec)

前の記事で作成したproductsが表示されれば、準備できています。

CREATE TABLEでstock_historyを作成しよう

今回は、履歴IDを主キーにし、入出庫種別と数量にCHECK制約を設定します。

以下が作成SQLの全体です。ファイルとして保存する場合の名前はcreate_stock_history.sqlとします。まずはMySQLクライアントで実行してみましょう。

商品の入出庫を記録するstock_historyを作成するSQL

ファイル名： create_stock_history.sql

USE inventory_db;

CREATE TABLE stock_history (
    history_id INT NOT NULL AUTO_INCREMENT,
    product_id INT NOT NULL,
    movement_type VARCHAR(10) NOT NULL,
    quantity INT NOT NULL,
    recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (history_id),
    CONSTRAINT chk_stock_history_type
        CHECK (movement_type IN ('IN', 'OUT')),
    CONSTRAINT chk_stock_history_quantity
        CHECK (quantity > 0)
) ENGINE=InnoDB
  DEFAULT CHARACTER SET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci;

実行結果の例

Query OK, 0 rows affected (0.03 sec)

処理時間は環境によって異なります。

Query OKが表示されれば、テーブルの作成は成功です。この段階では履歴を追加していないため、保存されている行数は0です。

SQL内のINとOUTを囲む引用符は、文字列データを表すために必要な構文です。命令や列名を囲むためのものではありません。

列と制約の役割を確認しよう

history_idは履歴ごとに自動採番する

history_idには、INT、NOT NULL、AUTO_INCREMENTを指定しています。さらに、PRIMARY KEYで主キーにしています。

履歴を登録するときにhistory_idを省略すると、MySQLが番号を割り当てます。

商品IDと同様に、自動採番された履歴IDにも欠番が生じることがあります。履歴IDの最大値が、そのまま履歴の件数になるとは限りません。件数を調べるときは、後で紹介するCOUNT(*)を使います。

また、履歴の表示順はSELECTのORDER BYで指定します。主キーがあるだけで、取得結果の並び順が保証されるわけではありません。

product_idはproductsと同じ型にする

stock_historyのproduct_idには、productsのproduct_idと同じINTを指定しています。

今回は両方ともUNSIGNEDを付けていないため、符号付きのINTです。

次の記事で外部キーを設定するときには、このように対応する整数列の型と符号の有無をそろえる必要があります。dev.mysql.com

ただし、今回のSQLには、まだ外部キー制約を書いていません。

列名をproduct_idにそろえただけでは、productsに存在する商品かどうかは検査されません。

存在しない商品のIDを履歴に保存できないようにする仕組みは、次の記事で追加します。

movement_typeはCHECKで種別を制限する

movement_typeはVARCHAR(10)なので、型の指定だけなら、最大10文字のさまざまな文字列を保存できます。

そこで、CHECK制約を使って、入出庫種別に使う値を制限します。

今回の条件では、IN演算子を使っています。IN演算子は、値が括弧内の候補のいずれかに一致するかを調べます。

同じINという表記が出てきますが、演算子と入庫を表すデータでは役割が異なります。

MySQL 8.4では、今回のように定義したCHECK制約が検査されます。条件を満たさない値を通常のINSERTやUPDATEで保存しようとすると、エラーになります。

なお、今回の照合順序utf8mb4_0900_ai_ciは、大文字と小文字を区別しません。そのため、小文字のinやoutも、このCHECK条件では一致すると判定されます。アプリから保存する表記は、大文字のINとOUTに統一する方針にします。

quantityは1以上にする

quantityには、CHECKでquantity > 0という条件を設定しています。

これにより、0や負の数量を保存できないようにします。

productsのstockでは0を許可しましたが、stock_historyのquantityでは0を許可しません。

在庫が0個であることは有効な状態です。一方、今回の設計では、0個の入出庫は記録の対象にしないという違いがあります。

また、quantityにはDEFAULTを設定していません。何個入出庫したのかは、その都度明示して登録します。

recorded_atは履歴を登録した日時

recorded_atにはDATETIMEを使い、DEFAULT CURRENT_TIMESTAMPを設定しています。

履歴の登録時にrecorded_atを省略すると、その時点の日時が入ります。ON UPDATE CURRENT_TIMESTAMPは指定していないため、ほかの列を変更しても、それだけで日時が自動更新されることはありません。

この列は、履歴をデータベースに記録した日時として扱います。

たとえば、昨日の入庫を今日入力した場合、省略時に入るのは今日の登録日時です。実際に荷物が届いた日時も別に管理したい場合は、そのための列を追加する設計が考えられます。

この記事では、まず記録日時を1つ持つシンプルな構成で進めます。

図2：入出庫の方向と数量を分けて記録する

この図から分かること

入庫でも出庫でも、quantityには正の数量を保存します。

在庫を増やすか減らすかはmovement_typeで判断するため、出庫を表すために数量を負にする必要はありません。方向と個数を分けることで、Cプログラムからも処理を組み立てやすくなります。

テーブル全体の設定をproductsとそろえよう

stock_historyでも、productsと同じテーブル設定を使っています。

InnoDBを使うことで、今後扱う外部キーやトランザクションを利用できます。

文字セットと照合順序も、データベースや商品テーブルとそろえています。後から作成SQLを読み返したときにも設定を確認しやすいよう、今回も明示しています。

作成したテーブルの構造を確認しよう

SHOW TABLESで2つのテーブルを確認する

SHOW TABLES;

実行結果の例

+------------------------+
| Tables_in_inventory_db |
+------------------------+
| products               |
| stock_history          |
+------------------------+
2 rows in set (0.00 sec)

productsとstock_historyの2つが表示されれば、在庫管理アプリで使う基本のテーブルがそろいました。

DESCRIBEで5つの列を確認する

DESCRIBE stock_history;

実行結果の例

+---------------+-------------+------+-----+-------------------+-------------------+
| Field         | Type        | Null | Key | Default           | Extra             |
+---------------+-------------+------+-----+-------------------+-------------------+
| history_id    | int         | NO   | PRI | NULL              | auto_increment    |
| product_id    | int         | NO   |     | NULL              |                   |
| movement_type | varchar(10) | NO   |     | NULL              |                   |
| quantity      | int         | NO   |     | NULL              |                   |
| recorded_at   | datetime    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+---------------+-------------+------+-----+-------------------+-------------------+
5 rows in set (0.00 sec)

表示上の大文字と小文字や、括弧の有無などが異なる場合があります。

次の点を確認しましょう。

• 5つの列が存在する。
• history_idのKeyがPRIになっている。
• history_idのExtraにauto_incrementが表示されている。
• すべての列のNullがNOになっている。
• recorded_atに日時の既定値が設定されている。

product_idのKeyが空欄なのは、この段階では主キーやインデックスを設定していないためです。同じ商品について複数の履歴を登録できる設計になっています。

また、Default欄のNULLは、必ずしもNULLを保存できることを意味しません。NULLを許可するかどうかは、Null欄で確認します。

SHOW CREATE TABLEでCHECK制約を確認する

列の一覧だけでなく、制約も含めて確認するにはSHOW CREATE TABLEを使います。

SHOW CREATE TABLE stock_history\G

末尾の\Gは、結果を縦方向に表示するMySQLクライアントの指定です。

実行結果の例

*************************** 1. row ***************************
       Table: stock_history
Create Table: CREATE TABLE `stock_history` (
  `history_id` int NOT NULL AUTO_INCREMENT,
  `product_id` int NOT NULL,
  `movement_type` varchar(10) NOT NULL,
  `quantity` int NOT NULL,
  `recorded_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`history_id`),
  CONSTRAINT `chk_stock_history_quantity` CHECK ((`quantity` > 0)),
  CONSTRAINT `chk_stock_history_type` CHECK ((`movement_type` in (_cp932'IN',_cp932'OUT')))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

MySQLが整形した定義なので、制約の表示順や括弧などは、入力したSQLと異なることがあります。文字列の前に表示される_utf8mb4は、その文字列の文字セットを示すものです。

確認したいのは、主に次の3点です。

COUNT(*)で履歴の件数を確認する

SELECT COUNT(*) AS history_count
FROM stock_history;

実行結果の例

+---------------+
| history_count |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

COUNT(*)は、テーブルに保存された行数を数えます。AS history_countは、結果に表示する列名を指定しています。

今回はテーブルを作成しただけなので、履歴は0件です。冒頭の入出庫の例は、まだデータベースには登録されていません。

在庫数の更新と履歴の追加は別の操作

ここで、今後のCプログラムを作るうえで大切な点を確認しておきましょう。

今回作成した2つのテーブルは、現時点では自動的に同期しません。

stock_historyに入庫の履歴を追加しても、それだけではproductsのstockは増えません。逆に、productsのstockだけを変更しても、履歴は追加されません。

在庫管理アプリでは、1回の入出庫について、次の2つをそろえて実行する必要があります。

• productsの現在の在庫数を更新する。
• stock_historyへ入出庫の履歴を追加する。

片方だけが成功すると、在庫数と履歴が食い違ってしまいます。今後は、この2つをまとめて成功または取り消しにするトランザクションを使います。

また、quantity > 0というCHECK制約は、出庫できるだけの在庫があるかまでは検査しません。出庫時の在庫確認も、後で作る処理の役割になります。

図3：在庫の更新と履歴の追加をそろえる

この図から分かること

1回の出庫には、現在の在庫数を減らす処理と、出庫の履歴を残す処理が必要です。

今回作成したのは、それぞれの情報を保存するテーブルです。2つの処理をそろえて実行する仕組みは、今後Cプログラムから在庫を操作するときに実装していきます。

テーブル作成でエラーが出たときは

作成に失敗した場合は、エラーの内容に合わせて確認します。

CHECKの条件には丸括弧が含まれるため、閉じ忘れに注意しましょう。また、INとOUTは文字列データなので、SQL内では半角のシングルクォートが必要です。

stock_historyがすでに存在する場合は、すぐに削除せず、まず現在の定義を確認してください。CREATE TABLEにIF NOT EXISTSを付けても、既存のテーブルが今回の設計に修正されるわけではありません。

次は商品と履歴を関連付けよう

inventory_dbに、商品を保存するproductsと、入出庫を記録するstock_historyがそろいました。

次に必要なのは、履歴に保存する商品IDが、実際にproductsに存在することを保証する仕組みです。

次の記事では、product_idを使って2つのテーブルを外部キーで関連付けます。商品を削除しようとしたときに、すでに保存されている履歴をどう扱うかも、一緒に考えていきましょう。

Follow me!

おすすめ記事

C言語＋MySQL入門｜商品と履歴を関連付ける Java超｜メソッドのオーバーライド Java超｜クラスとメソッドのしくみ Java道｜メソッドのオーバーライド C言語＋MySQL入門｜商品テーブルを作成する Java道｜メソッド（必殺技）を作って使う Java道｜インターフェイスで共通ルールを作る Java超｜スーパークラス型とポリモーフィズム