標準に沿ったスキーマ設計と制約の検証

目標

データ標準(単語→ドメイン→用語)に沿ってスキーマを設計し、制約が実際に動作することを確認し、照会パターンに合ったインデックスを作り、テーブル定義書をメタデータから自動生成できるようになります。

なぜ重要なのか

同じ顧客名がCUST_NAME(50)、CUSTOMER_NM(100)、CUST_NM(30)に散らばっているシステムは、統合照会で苦労し、短いカラムでデータが黙って切り詰められ、3年後の移行でマッピング表を人が手で作ることになります。そして、テーブル定義書に「必須」と書いてあるのにDDLにNOT NULLがなければ、その文書は守られません。制約はDBに設定して初めて強制されます。最後に、手で維持する定義書は、開発が終わるころには必ず実際とずれます。メタデータから取り出す習慣が、この問題をなくします。

ステップ

1. 要件: /opt/lab/fixtures/dbo/req/schema-req.md 標準単語: /opt/lab/fixtures/dbo/std/standard-words.csv
2. /root/dbを作成し、sqliteのDB/root/db/si.dbを作成してください。 (テーブルが1つでもないとファイルは作られません。ステップ3で一緒に行っても構いません。)
3. /root/db/naming.csvを作成してください。1行目はlogical,physical,domainです。 要件に出てくる論理名を標準単語で組み合わせて、物理名を決めます。 10行以上で、物理名は大文字とアンダースコアだけを使い、domainは空であってはいけません。 고객명、주문번호、주문일자、주문금액、상품코드の5つの論理名は必ず含めてください(韓国語の論理名で、順に顧客名、注文番号、注文日付、注文金額、商品コードを意味します)。
4. 次の4つのテーブルを作成してください。 CUSTOMER、PRODUCT、ORDERS、ORDER_ITEM 要件:
   • すべてのテーブルにPRIMARY KEY
   • ORDERS.CUST_ID → CUSTOMER、ORDER_ITEM.ORD_NO → ORDERS、 ORDER_ITEM.PROD_CD → PRODUCTにFOREIGN KEY
   • ORDER_ITEM.QTYに正の値だけを許可するCHECK
   • ORDERS.ORD_STS_CDに'01','02','03','09'だけを許可するCHECK
   • すべてのテーブルにREG_DTカラムとNOT NULL、デフォルト値
   • CUSTOMER.CUST_EMAILにUNIQUE
5. /root/db/constraint.txtを作成してください。3行で、各行に違反を試みた結果のメッセージの1行目を入れます。

   qty=<QTY 를 0 으로 INSERT 했을 때 오류 메시지>
   status=<ORD_STS_CD 를 '99' 로 INSERT 했을 때 오류 메시지>
   email=<CUST_EMAIL 을 중복으로 INSERT 했을 때 오류 메시지>
   

   (山括弧の中の韓国語はプレースホルダーで、QTYを0でINSERTしたとき、ORD_STS_CDを'99'でINSERTしたとき、CUST_EMAILを重複させてINSERTしたときの、それぞれのエラーメッセージです。) (元のDBを汚さず、コピーで試してください。)
6. インデックスを3つ作成してください。
   • IX_ORDERS_01: WHERE CUST_ID = ? AND ORD_DT BETWEEN ? AND ?用
   • IX_ORDERS_02: WHERE ORD_DT = ? AND ORD_STS_CD = ?用
   • IX_ORDER_ITEM_01: WHERE PROD_CD = ?用 カラム順序まで合っている必要があります。
7. /opt/lab/fixtures/dbo/seed/のCSVファイル4つを各テーブルにロードしてください。 CUSTOMERは200行、PRODUCTは50行、ORDERSは1000行、ORDER_ITEMは2400行である必要があります。
8. ビューV_DAILY_SALESを作成してください。 カラムはORD_DT、ORD_CNT、AMT_SUMで、 キャンセル状態(09)を除いた注文だけを集計し、ORD_DTの昇順です。
9. /root/db/table-def.csvを作成してください。1行目はtable,column,type,notnull,pkです。 DBメタデータから取り出して、4つのテーブルのすべてのカラムを入れます。 テーブル名もカラムの順序も、そのままにします。

参考

• sqliteの外部キーを有効にする: PRAGMA foreign_keys = ON;(接続ごとに設定する必要があります)
• メタデータ: PRAGMA table_info(<테이블>);、PRAGMA foreign_key_list(<테이블>);(プレースホルダーはテーブル名です)
• CSVのロード: .mode csv / .import --skip 1 <파일> <테이블>(プレースホルダーはファイル名とテーブル名です)
• よくあるミス1: PRAGMA foreign_keysを有効にせず、FKが検査されないことです。 sqliteはデフォルトでオフです。
• よくあるミス2: CHECKを文書にだけ書き、DDLに入れないことです。
• よくあるミス3: 複合インデックスのカラム順序を逆にしてしまうことです。

DBを作成する

/root/dbを作成し、sqliteのDB/root/db/si.dbを作成してください。 (テーブルが1つでもないとファイルは作られません。ステップ3で一緒に行っても構いません。)

sqliteは1つのファイルが1つのDBです。外部キーの検査がデフォルトでオフになっている点を覚えておいてください。

標準用語のマッピング表

/root/db/naming.csvを作成してください。1行目はlogical,physical,domainです。 要件に出てくる論理名を標準単語で組み合わせて、物理名を決めます。 10行以上で、物理名は大文字とアンダースコアだけを使い、domainは空であってはいけません。 고객명、주문번호、주문일자、주문금액、상품코드の5つの論理名は必ず含めてください(韓国語の論理名で、順に顧客名、注文番号、注文日付、注文金額、商品コードを意味します)。

標準単語辞書を組み合わせて物理名を作ります。同じ意味の単語が2回使われたら、略語も同じでなければなりません。

テーブルを作成する

次の4つのテーブルを作成してください。 CUSTOMER、PRODUCT、ORDERS、ORDER_ITEM 要件:

• すべてのテーブルにPRIMARY KEY
• ORDERS.CUST_ID → CUSTOMER、ORDER_ITEM.ORD_NO → ORDERS、 ORDER_ITEM.PROD_CD → PRODUCTにFOREIGN KEY
• ORDER_ITEM.QTYに正の値だけを許可するCHECK
• ORDERS.ORD_STS_CDに'01','02','03','09'だけを許可するCHECK
• すべてのテーブルにREG_DTカラムとNOT NULL、デフォルト値
• CUSTOMER.CUST_EMAILにUNIQUE

制約は文書ではなくDDLにあって初めて強制されます。必須項目、コード値の範囲、業務キーの重複防止を、それぞれどの制約で表現するかを考えてください。

制約の動作を確認する

/root/db/constraint.txtを作成してください。3行で、各行に違反を試みた結果のメッセージの1行目を入れます。

qty=<QTY 를 0 으로 INSERT 했을 때 오류 메시지>
status=<ORD_STS_CD 를 '99' 로 INSERT 했을 때 오류 메시지>
email=<CUST_EMAIL 을 중복으로 INSERT 했을 때 오류 메시지>

(山括弧の中の韓国語はプレースホルダーで、QTYを0でINSERTしたとき、ORD_STS_CDを'99'でINSERTしたとき、CUST_EMAILを重複させてINSERTしたときの、それぞれのエラーメッセージです。) (元のDBを汚さず、コピーで試してください。)

制約が実際に防ぐかを確認するには、違反するデータを入れてみる必要があります。元のDBを汚さないよう、コピーで試す習慣をつけてください。

照会パターンに基づくインデックス

インデックスを3つ作成してください。

• IX_ORDERS_01: WHERE CUST_ID = ? AND ORD_DT BETWEEN ? AND ?用
• IX_ORDERS_02: WHERE ORD_DT = ? AND ORD_STS_CD = ?用
• IX_ORDER_ITEM_01: WHERE PROD_CD = ?用 カラム順序まで合っている必要があります。

複合インデックスはカラム順序がすべてです。等価条件のカラムを前に、範囲条件のカラムを後ろに置くのが基本ルールです。

サンプルデータをロードする

/opt/lab/fixtures/dbo/seed/のCSVファイル4つを各テーブルにロードしてください。 CUSTOMERは200行、PRODUCTは50行、ORDERSは1000行、ORDER_ITEMは2400行である必要があります。

CSVのロード時は、ヘッダー行の処理と区切り文字の指定に注意してください。ロード後に件数を必ず確認します。

集計ビューを作成する

ビューV_DAILY_SALESを作成してください。 カラムはORD_DT、ORD_CNT、AMT_SUMで、 キャンセル状態(09)を除いた注文だけを集計し、ORD_DTの昇順です。

ビューは照会の標準を強制する手段でもあります。論理削除の条件をビューに入れておけば、条件の漏れによる事故を減らせます。

テーブル定義書を自動生成する

/root/db/table-def.csvを作成してください。1行目はtable,column,type,notnull,pkです。 DBメタデータから取り出して、4つのテーブルのすべてのカラムを入れます。 テーブル名もカラムの順序も、そのままにします。

文書を手で維持すると、やがて嘘になります。DBメタデータから取り出せば、常に実際と一致します。