データベースを扱っていると ID とコードがよく登場します。でも結構混同されてるような気がします。というか、実際今まで混同したデータベースもいくつも見てきました。以下、厳密な話ではなく、あくまでも経験に基づく ID とコードの話をいくつか書き出してみます。
定義
とりあえず e-Words の定義を参照してみます。
IDとは、識別、鑑別、同定、特定、身元確認、身分証明、身分証明書などの意味を持つ英単語。複数の同種の対象から特定の一つを識別、同定することや、そのような識別に用いられる名前や符号、数字、また、それらを記した文書やカードなどのことをIDという。
一般的には人物の特定に用いられる一意の識別番号などのことや、身分や身元を証明する書類やカード、標識などのことを指す場合が多い。ITの分野では利用者、登録者を識別するユーザー名やアカウント名などのことをIDと呼ぶことが多い。
また、人物に限らず、データやプログラム、機器や装置、また、それらの集合や構成要素など、コンピュータシステムの取り扱う様々な対象をそれぞれ一意に識別するための名前や符号、数字などのこともIDという。そのようなコンピュータにおける識別符号などを意味する場合はIDを “identifier” （識別子）の略とすることが多い。
コードとは、法典、規定、規約、記号、符号（化する）、暗号（化する）、などの意味を持つ英単語。
一般の外来語としては「ドレスコード」のように規約や規定を指す用法が多いが、ITの分野では「文字コード」のように規約によって定められた識別番号や識別符号などを指すことが多い。
情報システム上で対象の識別などに用いられるコードは短く一意（他と重複しない）であることが求められ、通し番号のように名前や意味とは無関係に機械的に割り当てられることが多い。
ID もコードも識別のために用いられることは変わりませんが、コードは「規約によって定められた」とあるのが大きな違いかなと思います。
個人的な経験に基づく ID とコードの違いは以下になります。
- ID
- システムが自動採番する。MySQL でいうところの auto_increment なカラム。数値自体に意味はない。
- UUID や Message-ID などもこちらなので、数値とは限らない。
- ID に対応するデータが必ず存在する。
- ID に対応するデータは一つだけ（2020.8.11追記)
- 一般にシステム内に閉じて使用される。
- コード
- 人がある程度の意図をもって採番する。1:男、2:女、9:その他 のように。数値に意味はないことが多いが、意味があることもある。
- M:明治、T:大正、S:昭和、H:平成、R:令和のように、こちらも文字の場合もある。
- コードに対応するデータが存在するとは限らない。
- コードに対応するデータは複数存在しうる（2020.8.11追記)
- コード体系を外部に公開することもある。郵便番号や電話番号のように、システム内部では ID で、外部提供する場合はコードとなる場合もある。
混ぜるな危険
混ぜるなといってもIDかコードのどちらかしか使うなというわけではありません。それぞれ必要な目的があるので、両方とも使っても大丈夫です。まずいのは、その区別が付いてなくて混同して使用される場合です。
よくあるのは、
create table users (
id integer,
:
);
create table items (
user_code integer,
:
);
なんてなっている場合です。ユーザIDなのかユーザコードなのか、どっちやねんってわけです。もちろん逆もあります。
create table genders
code integer,
:
);
create table persons
gender_id integer,
:
);
とかです。こうしてみると冗談のようであり得ないように思いますが、実際の現場ではよく見かけます。
また、外部から提供されるコードを格納する場合はさらにカオスになりやすい気がします。例えば郵便番号を格納する場合に、
create table companies (
zipcode char(7),
:
);
create table members (
zipid integer,
:
);
create table addresses (
zip_num integer,
:
);
とかなってると、混乱すること必至です。テーブルを追加する都度、その時の思い付きだけでカラム名を決めているとこうなってしまいますね。何でもかんでも規約を作るのも窮屈ですが、ある程度大きなシステムならば規約で縛っておくのも必要なのでしょうか。
型は合わせて欲しい
先ほどの郵便番号の例では、char型とinteger型に型が分かれていました。MySQLの場合は型が違ってもキャストして比較してくれるので、こんなテーブル設計でもそれなりに動いてしまったりするのですが、やっぱり気持ち悪いです。
ゼロ詰めするか
これはID/コードに限った話ではないのですが、数値を文字列型に格納する場合にゼロ詰めするかどうかはテーブル設計の段階で決めておいて欲しいです。あるテーブルではゼロ詰めし、あるテーブルではゼロ詰めをしない。それでもやっぱりMySQLでは数値にキャストすればうまく動いてしまったりするわけですが、これまたとてつもなく気持ち悪いです。
コードの定義場所
コードの場合はコード定義をどこでするかって問題があります。アプリ側に定数で定義してもいいんですが、データベースを参照するアプリは一つとは限らないので、その全てのアプリに定数定義するのも面倒ですし、コード追加時の同期をとるのも大変。なのでデータベース内にコード定義テーブルを持っているのが個人的な好みです。アプリとデータベースの寿命の不一致ってのもありますし。
外部制約
ID は当然外部制約かけるとして、コードはどうするか。外部成約をかけたほうがいいのは間違いないんですが、外部から提供されるコードの場合、コードが削除される場合があると困りますよね。そういう場合は、コードの更新時に不整合が起きないようにデータを修正する処理も作り込まないといけません。