データベースにおける記憶状態の解

アプリケーションを設計するとき、あなたはしばしば「状態」をモデル化する必要があります.この意味では、単一のエンティティに直接関係しています.以下に例を示します:
私には注文があります.

未定

有料

派遣

配達

キャンセル

払い戻し

順序は、いつでも単一の状態に関連付けられている可能性があります.両方の配送命令を返すのは論理的な意味ではない.
これらの状態の間の移行は、あなたのサービスの動作をドライブします.例えば、注文がディスパッチに移行するとき、配送追跡詳細の顧客に通知する理想的な時間です.
このタイプの解決策をモデル化するとき、エンジニアはしばしば現在の状態を記述するためにオリジナルの実体テーブルの上で一つのコラムを持つことの単純さによって誘惑されます.これは、いくつかの異なる方法で達成することができます.

最初の解決法

我々が議論したように、最も簡単な解決策は、注文テーブルに列を追加することです.この列は現在の状態を保存できます.
状態を文字列として格納することで実現できます.

CREATE TABLE `orders` (
 `id` INT unsigned NOT NULL AUTO_INCREMENT,
 `customer_id` INT unsigned NOT NULL,
 `order_value` INT NOT NULL,
 `state` VARCHAR NOT NULL,
 `created_at` DATE NOT NULL,
 `updated_at` DATE NOT NULL,
 PRIMARY KEY (`id`)
);

これは多くの理由で良くない.最も明白なものは、アプリケーションは、アプリケーションのために設計されていない状態の列に、任意の文字列を提出することができますです.これは、アプリケーションの原因とサービスの問題を引き起こすことができます.
私たちは、状態列をenumにし、期待値を許容するだけでその問題を解決できました.

ALTER TABLE orders ADD state enum('pending','paid',...);

これはまだ悪い解決です.

我々は新しい州を追加するたびにテーブルの変更を実行する必要があります.

我々は、特定の文字列が格納されている単一の列に制限されている状態について追加のメタデータを格納することはできません.

それは無効にする値に挑戦することができます.店がもはや払い戻しを提供しないならば、どうですか?

ENUM DataTypeは標準SQLではなく、多くのORMによってサポートされません.

我々はアプリケーションの状態の可能な値のリストを出力する場合は複雑です.

少しより良い溶液

状態を示す単一のカラムを有することは、理想的でない以前の方法から明らかである.我々が設計する次の論理的な解決策は注文状態テーブルを作成して、外部キーを通してオーダーテーブルからそれを参照することです.

CREATE TABLE `order_states` (
 `id` INT NOT NULL AUTO_INCREMENT,
 `key_name` VARCHAR NOT NULL,
 `created_at` DATE NOT NULL,
 `updated_at` DATE NOT NULL,
 PRIMARY KEY (`id`)
);
INSERT INTO order_states (key_name)
  VALUES('pending', 'paid',...);
ALTER TABLE orders 
ADD CONSTRAINT FK_OrderState 
FOREIGN KEY(state_id)
REFERENCES id(order_states);

これはいくつかの前の問題を解決します.新しい状態を追加するときにテーブルにALTERを実行する必要はなく、OrderRange状態テーブルの新しい列として状態に格納する必要のある追加のメタデータを追加できます.状態を無効にする/削除する必要がある場合は、stateound idで順序を選択するのは簡単ですし、また、外部キー制約を介して有効な状態の保護を取得します.

ベストソリューション

我々が実装した解決策は我々の問題のほとんどを解決しました、しかし、我々は時間とともに実体状態の可視性で問題を抱えています.順序は単一のFK参照を通して状態を割り当てられるので、それは我々が順序の現在の状態に関する情報しか持っていないことを意味します.注文が現在キャンセルされた状態であるならば、それが支払われたとき、私に話す簡単な方法がありません.
ピボットテーブルを追加することで解決できます.

CREATE TABLE `order_state_history` (
 `id` INT NOT NULL AUTO_INCREMENT,
 `order_id` INT unsigned NOT NULL,
 `order_state_id` INT NOT NULL,
 PRIMARY KEY (`id`)
);

このテーブルの作成に加えて、OrdersテーブルからStateKeep - ID列を削除します.
このピボットテーブルは次のように動作します.このテーブルに新しいレコードを挿入する順序を変更したい場合はいつでも.ここで任意の行を更新する必要はありません.また、任意の行を削除する必要はありません.
順序の現在の状態は、このテーブル(OrderRound IDに関連付けられた)で最も最近のレコードです
次のクエリを使用すると、最新の状態で順番を取得できます.

SELECT orders.*, order_state_history.*
FROM orders O
JOIN order_state_history OSH1 ON (O.id = OSH1.order_id)
LEFT OUTER JOIN order_state_history OSH2 ON (O.id = OSH2.order_id AND (OSH1.created_at < OSH2.created_at OR (OSH1.created_at = OSH2.created_at AND OSH1.id < OSH2.id)))
WHERE OSH2.id IS NULL;

欠点
私が少なくとも上記の解決の欠点のいくつかを指摘しなかったならば、それは完全でありません.

追加ピボットテーブルが必要な状態へのリンク順序

順序からデータを選択するには、より複雑なクエリが必要です(結合を含む).

各注文の追加行を格納する必要があります.最悪の場合はorder * count (

概要

それが挿入だけであるので、議論される最後の解決はより堅牢です.我々は、人種条件を心配する必要はありません.
更新のストリームを受信した場合は、各レコードを受信する順序で書き込みます.また、我々は秩序の完全な歴史的記録とそれが開催された状態があることを保証します.
これによりデータセットを見て、以下のような質問をすることができます.

どのように多くの受注を1/12/2021と31/12/22

月8日に返済された命令の合計値は何でしたか

これらの質問は、我々が州の動きの歴史を保持しなかったので、他の解決策に答えるのは不可能でした - 現在のものだけです.
書き込まれる追加のコードは最小限であり、適切なインデクシングを使用するとき、結合を使用することによって得られるどんなパフォーマンスも些細なことです.
追加のデータについてのどんな懸念も簡単に不要な状態履歴項目を削除する単純なクエリを実行することによって修正することができます.擬似符号語 - 

1年前にcreatedstraの場合はすべての項目を削除します

任意のレコードに対して、

このようなクエリを実行すると、歴史的な項目(1年以上前)を削除することになりますが、各順序の最新状態を保持します(これは重要なままです)
結論として、それはあなたのアプリケーションの状態を管理するはるかに良い、より完全な方法であり、実用的な場所で利用する必要があります.