データベース作成のときの実践的なテーブル構成方法を紹介します。
先に要点をまとめると、テーブルは「頻繁に増えるトランザクションテーブル」と「参照用のマスタテーブル」に分けて名前で区別し、全テーブルに削除フラグ・更新日時・更新者の列を共通で持たせます。外部キー制約は必ず付けるものではなく、運用に合わせて判断します。テーブル同士の関係を図にする段階では、ER図とIE記法の書き方もあわせて確認してください。
トランザクションテーブルとマスタテーブルの識別
トランザクションテーブル
トランザクションテーブルとは、phpなどアプリからデータを頻繁に挿入したり更新したりするようなテーブルです。
エントリーテーブルとも呼ばれます。
例えば、注文を受けつけたオーダー情報のテーブル、顧客情報のテーブル、請求情報のテーブルなどです。
先頭にENT, TXN, TRNなどを付けてトランザクションテーブルとわかりやすいテーブル名にします。
マスタテーブル
参照値を保持する用のテーブルです。
アプリからは頻繁には値を挿入、変更しないテーブルです。
商品一覧や、店舗一覧などです。
先頭にはMSTとつけてマスターであることを示します。
論理削除フラグの導入 – delete_flg
レコードの有効性を識別するためのフラグです。
delete_flgや、valid_recordなどの名前でフラグを作成します。
例えば、delete_flg = 1の場合には無効レコードとして扱うといった使い方をします。
物理削除（DELETE文）と違い、誤って消したデータを戻せる・履歴を追えるのが利点です。一方で、すべてのSELECTにWHERE delete_flg = 0が必要になり、ユニーク制約とも相性が悪くなります。最近は削除日時を入れるdeleted_at（NULLなら有効）を使う設計も多いので、どちらにするかはプロジェクトで統一します。
更新日、更新者の導入 – updated_at, updated_by
レコードがいつ、誰によって変更されたのかを保持するための属性で、updated_at, updated_byを付与します。
登録日（created_at, created_by）も同じように設けます。
バグの原因やいつのバグなのかがわかりやすくなります。
列の定義でDEFAULT CURRENT_TIMESTAMPとすると、INSERT時に現在日時が自動で入ります。
ON UPDATE CURRENT_TIMESTAMPも付けると、UPDATE時にも日時が自動で更新されます。
外部キー制約はつける場合とつけない場合がある
外部キー制約はとても狭い制約になるため、つけない場合もあります。
付けると参照先のない行を入れられなくなりデータの整合性を守れますが、削除順やテストデータ投入の順番に縛りが出て、大量データの更新も遅くなります。整合性をアプリ側で担保できるなら付けない、という判断も実務ではよくあります。
外部キー制約の作成方法については、【MySQL】SQLの書き方まとめを参考にしてみてください。