マスタ設計の考え方｜トランザクションとの違いと判定フロー・DDL例

マスタとは、商品・社員・部署のように「業務で繰り返し参照される台帳」を持つテーブルで、トランザクションとは、注文・入出庫・申請のように「出来事が起きるたびに1行増える記録」を持つテーブルです。 分けること自体は難しくありませんが、マスタ設計で差がつくのはその先で、マスタの値を後から変えると、過去の取引の表示や集計まで変わるという点をどう扱うかです。

この記事では、単価を変更した前後で過去の月の集計が変わる様子を PostgreSQL の実行結果で示し、「取引側に時点の値を写す」か「マスタに適用期間を持たせる」かを項目ごとに決める方法をまとめます。マスタかトランザクションか迷いやすいテーブル（在庫、設定値、区分値）の判定フロー、マスタ一覧のテンプレート、区分値を1つのコードマスタにまとめるかどうかの判断表も載せます。履歴テーブルの作り込みと、コードや番号の振り方はこの記事では扱いません。

確認環境（2026-10-05）：Ubuntu 24.04、PostgreSQL 16.15。Mermaid の図は @mermaid-js/mermaid-cli 12.0.0 と Chromium で描画できることを確かめています。

この記事の結論

• マスタは「参照される台帳」、トランザクションは「増えていく記録」。 行が増えるきっかけが、人の登録か、出来事の発生かで見分けます。
• マスタを上書きすると、それを参照する過去の取引の見え方も変わる。 下の例では、単価を200円から250円に変えただけで、先月の集計が5,000円から5,750円になりました。
• 対処は2つ。取引側に時点の値を写すか、マスタに適用期間を持たせるか。 テーブル単位ではなく、項目ごとに決めます。
• 在庫数のように取引から求まる値は、マスタではなく集計結果として扱う。
• 区分値は、属性がコードと名称だけなら汎用コードマスタ、その区分だけの属性や外部キーが要るなら個別マスタにする。

マスタとトランザクションの違い 早見表

マスタとトランザクションのどちらにも当てはまりにくいものが3つあります。後半の判定フローで振り分けます。

単価を変えると、先月の集計が変わる

商品マスタに単価を持ち、注文明細は商品コードと数量だけを持つ、という素直な設計から始めます。PostgreSQL 16 で実行しました。

CREATE TABLE products (
  product_code text PRIMARY KEY,
  product_name text    NOT NULL,
  unit_price   integer NOT NULL CHECK (unit_price >= 0),
  is_active    boolean NOT NULL DEFAULT true,
  updated_at   timestamptz NOT NULL DEFAULT now(),
  updated_by   text    NOT NULL
);
CREATE TABLE orders (
  order_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  ordered_on date NOT NULL
);
CREATE TABLE order_items (
  order_id     bigint  NOT NULL REFERENCES orders(order_id),
  line_no      integer NOT NULL,
  product_code text    NOT NULL REFERENCES products(product_code),
  quantity     integer NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, line_no)
);
INSERT INTO products (product_code, product_name, unit_price, updated_by) VALUES
  ('P001', 'ノートA4',   200, 'sato'),
  ('P002', 'ボールペン', 100, 'sato');
INSERT INTO orders (ordered_on) VALUES ('2026-09-10'), ('2026-09-25'), ('2026-10-03');
INSERT INTO order_items VALUES (1,1,'P001',10), (1,2,'P002',20), (2,1,'P001',5), (3,1,'P001',10);

月ごとの金額を集計します。

SELECT to_char(o.ordered_on, 'YYYY-MM') AS month, sum(i.quantity * p.unit_price) AS amount
FROM orders o JOIN order_items i USING (order_id) JOIN products p USING (product_code)
GROUP BY 1 ORDER BY 1;

  month  | amount 
---------+--------
 2026-09 |   5000
 2026-10 |   2000
(2 rows)

ここで、10月1日からの値上げとして P001 の単価を250円にし、名称も変えます。

UPDATE products
SET unit_price = 250, product_name = 'ノートA4（再生紙）', updated_at = now(), updated_by = 'suzuki'
WHERE product_code = 'P001';

同じ集計をもう一度実行した結果です。

  month  | amount 
---------+--------
 2026-09 |   5750
 2026-10 |   2500
(2 rows)

注文のデータには1行も触れていないのに、9月の金額が5,000円から5,750円に変わりました。 9月の注文は200円で受けたものですが、集計のたびに「いまのマスタの単価」を掛けているためです。10月の2,500円は正しい値で、変わってほしくなかったのは9月だけです。

対処A：取引側に時点の値を写す

注文明細に、注文した時点の単価と商品名を列として持たせます。すでにある order_items に列を足し、変更前の値で埋めました。

ALTER TABLE order_items ADD COLUMN unit_price integer, ADD COLUMN product_name text;

-- 9月の注文（order_id 1, 2）の P001 は、変更前の値で埋める
UPDATE order_items i
SET unit_price   = CASE WHEN i.order_id IN (1, 2) AND i.product_code = 'P001' THEN 200 ELSE p.unit_price END,
    product_name = CASE WHEN i.order_id IN (1, 2) AND i.product_code = 'P001' THEN 'ノートA4' ELSE p.product_name END
FROM products p WHERE p.product_code = i.product_code;

ALTER TABLE order_items ALTER COLUMN unit_price SET NOT NULL, ALTER COLUMN product_name SET NOT NULL;

-- 集計はマスタを見ず、明細の単価を使う
SELECT to_char(o.ordered_on, 'YYYY-MM') AS month, sum(i.quantity * i.unit_price) AS amount
FROM orders o JOIN order_items i USING (order_id)
GROUP BY 1 ORDER BY 1;

  month  | amount 
---------+--------
 2026-09 |   5000
 2026-10 |   2500
(2 rows)

明細の中身は次のとおりです。9月の行は変更前の名称と単価、10月の行は変更後の値を持っています。

 order_id | line_no | product_code |    product_name    | unit_price | quantity 
----------+---------+--------------+--------------------+------------+----------
        1 |       1 | P001         | ノートA4           |        200 |       10
        1 |       2 | P002         | ボールペン         |        100 |       20
        2 |       1 | P001         | ノートA4           |        200 |        5
        3 |       1 | P001         | ノートA4（再生紙） |        250 |       10
(4 rows)

この例のように後から列を足す場合、変更前の値（200円、ノートA4）はマスタにもう残っていないので、別の記録から調べて埋めることになります。 最初の設計の段階で写す項目を決めておくと、この作業が要りません。

写した値は「その時点の事実」なので、正規化に反する重複ではありません。マスタの単価は「いま注文したらいくらか」、明細の単価は「この注文はいくらで受けたか」で、意味が違う別の項目です。

対処B：マスタに適用期間を持たせる

単価を、いつからいつまで有効かという期間つきで持つ方法です。

CREATE TABLE product_prices (
  product_code text    NOT NULL REFERENCES products(product_code),
  valid_from   date    NOT NULL,
  valid_to     date    NOT NULL DEFAULT '9999-12-31',   -- この日を含まない
  unit_price   integer NOT NULL CHECK (unit_price >= 0),
  PRIMARY KEY (product_code, valid_from),
  CHECK (valid_from < valid_to)
);
INSERT INTO product_prices VALUES
  ('P001', '2026-04-01', '2026-10-01', 200),
  ('P001', '2026-10-01', '9999-12-31', 250),
  ('P002', '2026-04-01', '9999-12-31', 100);

SELECT to_char(o.ordered_on, 'YYYY-MM') AS month, sum(i.quantity * pp.unit_price) AS amount
FROM orders o JOIN order_items i USING (order_id)
JOIN product_prices pp ON pp.product_code = i.product_code
                      AND o.ordered_on >= pp.valid_from AND o.ordered_on < pp.valid_to
GROUP BY 1 ORDER BY 1;

  month  | amount 
---------+--------
 2026-09 |   5000
 2026-10 |   2500
(2 rows)

注文日が入る期間の単価を結合しているので、9月は200円、10月は250円で計算されます。来月からの値上げを前もって登録しておける点も、この方法の利点です。

期間に抜けがあると、行が集計から消える

適用期間の方式には見落としやすい点があります。単価の登録が始まる前の日付（2026-03-20）の注文を1件足して、明細の件数と、単価が見つかった件数を比べました。

INSERT INTO orders (ordered_on) VALUES ('2026-03-20');
INSERT INTO order_items VALUES (4, 1, 'P002', 30, 100, 'ボールペン');

SELECT count(*) AS items, count(pp.product_code) AS priced
FROM orders o JOIN order_items i USING (order_id)
LEFT JOIN product_prices pp ON pp.product_code = i.product_code
                           AND o.ordered_on >= pp.valid_from AND o.ordered_on < pp.valid_to;

 items | priced 
-------+--------
     5 |      4
(1 row)

明細は5件あるのに、単価が見つかったのは4件です。内部結合（JOIN）で集計していると、この1件はエラーにならず、金額から静かに抜けます。 適用期間を持たせるときは、期間に抜けや重なりがないことを登録時に確かめる仕組みと、上のような件数の突き合わせを合わせて用意します。

写すか、適用期間か：項目ごとの選び方

条件から選ぶときの目安です。

AとBは二者択一ではありません。 単価は適用期間つきのマスタで管理し、注文の時点でその単価を明細に写す、という組み合わせがよく使われます。マスタは「いつからいくらか」を、明細は「この注文はいくらだったか」を受け持ちます。

項目ごとに決めた結果は、次のような表にして設計書に残します。

マスタかトランザクションかの判定フロー

flowchart TD
    A([このテーブルはどちらか]) --> B{出来事が起きるたびに<br/>行が増えるか}
    B -- はい --> T1[トランザクション<br/>例：注文、入出庫、申請]
    B -- いいえ --> C{ほかのテーブルから<br/>キーで参照されるか}
    C -- いいえ --> D{システムの動きを<br/>変える値か}
    D -- はい --> S1[設定値<br/>キーと値で1か所に持つ]
    D -- いいえ --> T2[トランザクション側の<br/>列として持つ]
    C -- はい --> E{値を決めるのは<br/>人の登録・変更か}
    E -- いいえ --> T3[集計結果・残高<br/>例：在庫数。取引から求める]
    E -- はい --> F{属性はコードと名称<br/>だけか}
    F -- はい --> K1[区分値<br/>コードマスタまたは個別マスタ]
    F -- いいえ --> M1[マスタ<br/>例：商品、社員、部署]
    M1 --> G{過去の取引に<br/>変更前の値が必要か}
    K1 --> G
    G -- はい --> H[取引側に写す<br/>または適用期間を持たせる]
    G -- いいえ --> I[上書き更新でよい]

迷いやすい3つを、このフローに通した結果です。

在庫を「商品マスタの列」として持つと、数量が変わるたびにマスタが更新され、マスタの更新日時や更新者が「だれが商品情報を変えたか」を表さなくなります。台帳としての情報と、動いていく数量は分けておくと扱いやすくなります。

区分値：汎用コードマスタにまとめるか、個別マスタにするか

区分値の持ち方は2通りあります。1つは、すべての区分を「区分の種類・コード・名称」の1テーブルにまとめる汎用コードマスタ、もう1つは区分ごとにテーブルを作る個別マスタです。

汎用コードマスタは、そのままでは外部キーを張れない

CREATE TABLE codes (
  code_type text NOT NULL,
  code      text NOT NULL,
  name      text NOT NULL,
  sort_no   integer NOT NULL DEFAULT 0,
  is_active boolean NOT NULL DEFAULT true,
  PRIMARY KEY (code_type, code)
);
INSERT INTO codes VALUES
  ('order_status', '10', '受付', 1), ('order_status', '20', '出荷済み', 2),
  ('shipping_method', '10', '宅配', 1), ('shipping_method', '90', '店頭受取', 2);

CREATE TABLE shipments_a (
  shipment_id bigint PRIMARY KEY,
  status_code text NOT NULL
);
ALTER TABLE shipments_a ADD FOREIGN KEY (status_code) REFERENCES codes(code);

ERROR:  there is no unique constraint matching given keys for referenced table "codes"

codes の主キーは「種類＋コード」の2列なので、コードの列だけでは外部キーを張れません。外部キーなしのままだと、注文の状態には存在しない '90'（配送方法のコード）も入ります。

INSERT INTO shipments_a VALUES (1, '90');

SELECT s.shipment_id, s.status_code, c.name
FROM shipments_a s LEFT JOIN codes c ON c.code_type = 'order_status' AND c.code = s.status_code;

 shipment_id | status_code | name 
-------------+-------------+------
           1 | 90          | 
(1 row)

登録は通り、名称が空のまま表示されます。

種類を固定した列を足すと、外部キーを張れる

汎用コードマスタのまま整合性を保つには、参照する側に「種類」を固定した列を持たせ、2列で外部キーを張ります。

CREATE TABLE shipments_b (
  shipment_id bigint PRIMARY KEY,
  status_type text NOT NULL DEFAULT 'order_status' CHECK (status_type = 'order_status'),
  status_code text NOT NULL,
  FOREIGN KEY (status_type, status_code) REFERENCES codes(code_type, code)
);
INSERT INTO shipments_b (shipment_id, status_code) VALUES (1, '10');   -- 通る
INSERT INTO shipments_b (shipment_id, status_code) VALUES (2, '90');

ERROR:  insert or update on table "shipments_b" violates foreign key constraint "shipments_b_status_type_status_code_fkey"
DETAIL:  Key (status_type, status_code)=(order_status, 90) is not present in table "codes".

個別マスタは、その区分だけの属性を持てる

CREATE TABLE order_statuses (
  status_code text PRIMARY KEY,
  status_name text NOT NULL,
  sort_no     integer NOT NULL,
  can_cancel  boolean NOT NULL           -- この区分だけが持つ属性
);
INSERT INTO order_statuses VALUES ('10', '受付', 1, true), ('20', '出荷済み', 2, false);

CREATE TABLE shipments_c (
  shipment_id bigint PRIMARY KEY,
  status_code text NOT NULL REFERENCES order_statuses(status_code)
);
INSERT INTO shipments_c VALUES (1, '90');

ERROR:  insert or update on table "shipments_c" violates foreign key constraint "shipments_c_status_code_fkey"
DETAIL:  Key (status_code)=(90) is not present in table "order_statuses".

外部キーは1列で済み、「取り消しできる状態か」のような区分固有の属性を列として持てます。

判断表

1つのシステムの中で両方を使い分けて構いません。プログラムが意味を知っている区分は個別マスタ、表示用の選択肢は汎用コードマスタ、という分け方にすると、汎用コードマスタに予備の列が増えていくのを避けられます。

マスタの削除方針

取引から参照されているマスタの行は、外部キーがあれば削除できません。

DELETE FROM products WHERE product_code = 'P002';

ERROR:  update or delete on table "products" violates foreign key constraint "order_items_product_code_fkey" on table "order_items"
DETAIL:  Key (product_code)=(P002) is still referenced from table "order_items".

そこで、使わなくなったマスタは行を残したまま「新しい取引では選べない」状態にします。

UPDATE products SET is_active = false WHERE product_code = 'P002';
SELECT product_code, product_name, is_active FROM products ORDER BY 1;

 product_code |    product_name    | is_active 
--------------+--------------------+-----------
 P001         | ノートA4（再生紙） | t
 P002         | ボールペン         | f
(2 rows)

選択肢に出す問い合わせは is_active = true で絞り、過去の取引の表示では絞らない、という使い分けを画面ごとに決めておきます。

マスタ一覧のテンプレート

設計書に載せるマスタ一覧です。架空の備品予約システムを題材に記入例を入れています。

表計算ソフトに貼れるタブ区切りです。

No  マスタ名    テーブル名   キー  主な項目    更新者 更新頻度    件数の目安   削除方針    履歴の持ち方
1   備品マスタ   equipments  備品コード   名称、分類、保管場所、貸出可否 総務担当    月に数件    500 削除しない（無効化）  名称は予約に写す。ほかは上書き
2   社員マスタ   employees   社員番号    氏名、メールアドレス、在籍状態 人事システムから日次で取り込み 毎日  300 削除しない（退職で無効化）   氏名は上書き
3   所属マスタ   employee_departments    社員番号＋開始日    部署コード、終了日   人事システムから日次で取り込み 月に数件    1,000   削除しない   適用期間
4   部署マスタ   departments 部署コード   部署名、上位部署    管理者 年に数回    30  削除しない（無効化）  部署名は上書き
5   予約状態    reservation_statuses    状態コード   名称、並び順、取り消し可否   開発者（リリースで変更）    ほぼなし    5   削除しない   上書き
6   コードマスタ  codes   種類＋コード  名称、並び順、有効フラグ    管理者 年に数回    50  削除しない（無効化）  上書き
7   設定値 settings    設定キー    値、説明    管理者 年に数回    20  削除できる   変更の記録を残す

各列に書くことは次のとおりです。

「更新者」の列でリリースで変えるとしたマスタは、利用者が画面から変えるマスタとは別の扱いになります。プログラムがコードの値を知っている区分は、値の追加と処理の追加が必ずセットになるためです。

確かめて分かったこと

• 単価を1件上書きしただけで、注文データに触れずに先月の集計が5,000円から5,750円に変わった。 エラーも警告も出ないため、気づくのは数字を見比べたときです。
• 後から列を足して写す場合、変更前の値はマスタに残っていない。 今回は200円と分かっていたので埋められましたが、設計時に写す項目を決めておくほうが確実です。
• 適用期間の方式では、期間の外にある注文が内部結合で集計から抜けた。 明細5件のうち単価が付いたのは4件で、エラーにはなりません。
• 汎用コードマスタにコードの列だけで外部キーを張ろうとすると、PostgreSQL は there is no unique constraint matching given keys というエラーを返した。 種類を固定した列を足して2列で張ると、別の区分のコード '90' を止められました。
• 参照されているマスタの行は、外部キーがあれば DELETE がエラーで止まる。 無効化の列を用意しておくと、過去の取引の表示を保ったまま選択肢から外せます。

よくある質問

Q. マスタとトランザクションの違いは何ですか？ A. マスタは繰り返し参照される台帳、トランザクションは出来事のたびに増える記録です。商品や社員はマスタ、注文や入出庫はトランザクションです。行が増えるきっかけが人の登録か出来事の発生かで見分けられます。

Q. マスタを変更すると、過去のデータはどうなりますか？ A. 取引がマスタをコードで参照しているだけなら、過去の取引の表示や集計も変更後の値になります。変えたくない項目は、取引側にその時点の値を写すか、マスタに適用期間を持たせます。

Q. 取引テーブルにマスタの値を写すのは、正規化に反しませんか？ A. 反しません。マスタの単価は「いまの値」、取引に写した単価は「その取引の時点の値」で、意味の違う別の項目だからです。写すのは、過去の値として残す必要がある項目に絞ります。

Q. 在庫はマスタですか、トランザクションですか？ A. どちらでもなく、入出庫というトランザクションから求まる集計結果です。現在の数を別のテーブルに持つ場合も、入出庫の記録と同じトランザクションで更新し、ずれたときに入出庫から計算し直せるようにします。

Q. 区分値は全部1つのコードマスタにまとめてよいですか？ A. 表示用の選択肢ならまとめて構いません。プログラムが値で分岐する区分や、その区分だけの属性を持つものは、個別マスタにすると外部キーが1列で張れて扱いやすくなります。

まとめ

• マスタは参照される台帳、トランザクションは出来事のたびに増える記録。
• マスタを上書きすると、過去の取引の表示と集計も変わる。 単価の変更で、先月の集計が5,000円から5,750円になった。
• 対処は「取引側に写す」と「適用期間を持たせる」の2つで、項目ごとに決める。 金額に使う値と印字した値は写す。
• 適用期間を持たせるときは、期間の抜けで行が集計から消えないよう件数を突き合わせる。
• 在庫は集計結果、設定値はキーと値、区分値はコードマスタか個別マスタに振り分ける。
• 汎用コードマスタは、種類を固定した列を足すと外部キーを張れる。 固有の属性や分岐があるなら個別マスタにする。
• マスタ一覧には、キー・更新者・更新頻度・削除方針・履歴の持ち方を書く。

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。