usersテーブルの構成カラムについて

質問

MySQLバージョン4.1.16を使用しています。
このジャンルでお願いします。

よく全ての基本となるusersテーブル（IDとパスワードを入れるユーザの基本テーブル）
があると思うのですが、それを構成するカラムはどのような感じにするのが良いのでしょうか？
自分は、
CREATE TABLE `users` (
`user_id` varchar(16),
`password` varchar(255) default NULL,
`col1` varchar(255) NOT NULL,
`col2` varchar(255) NOT NULL,
PRIMARY KEY (`user_id`),
) ENGINE=InnoDB;
このように代替キー（連番）を使わずにuser_idを主キー（unique）にして構成します。
理由としては、uniqueな列があるなら、わざわざ連番を割り当てる必要もないし、
他のテーブルがuser_idを外部キーとして持った時に、その表の外部キーの列が
数字であるよりも見やすいかなと思ったからです。
ただ一般的に出まわっているPHPフレームワークなどのテーブル構成ではusersテーブルというか
ほとんどのテーブルに連番を使っている例があります。
次のusersテーブルは、PHPフレームワークのCakePHPで使われるusersテーブルの構成の例で、
連番を使用しています。
CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(255) collate utf8_unicode_ci default NULL,
`password` varchar(255) collate utf8_unicode_ci default NULL,
`group_id` int(11) NOT NULL,
`disabled` tinyint(1) NOT NULL,
`created` datetime default NULL,
`modified` datetime default NULL,
PRIMARY KEY (`id`),
KEY `group_id` (`group_id`),
KEY `disabled` (`disabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

使用状況によって、どちらが良い悪いというのは変わるのでしょうけど
基本的にusersテーブルというのはどのような構成にすべきなのでしょうか？
ちなみに自分は連番は○○_seqというカラム名にしてます（ただこれも
一般的には○○idを連番にしてるケースが多いので、どうすべきか迷っています・・・）

回答No.2

chukenkenkou · ベストアンサー

代替キー（RDBMSにより、一意な連番を生成する機能を活用）は、部署コードなど「コード体系が変更される可能性が高い場合」に使うと便利な機能です。
従業員番号やユーザIDなど、一意でかつ「コード体系が変更される可能性が殆どない場合」には、あえて使う必要はありません。

操作ログを残していくようなテーブルでは、auto_incrementは便利ですね。auto_incrementの列だけ、あるいは（日時＋auto_increment）で一意にして、時系列に操作を追跡できますから。

お礼

ご返答ありがとうございます。
お二方の意見で、自分の考えもまとまりました。ありがとうございます。

ところでこれは別の質問になっちゃうかもしれないのですが、
お答え頂けないでしょうか？
int型（最小-2147483648, 最大2147483647）のauto_incrementの場合だと
21億以上は増やせないと思うのですが、これはbigint型（最小-9372036854775808, 最大9372036854775807）
にした方がいいのでしょうか？
まあ普通はそこまで行が増えるということはないのでしょうけど
ログなどのテーブルで詰め込み方次第で21億以上増えないとは言い切れないですし、なんとなく気持ち悪いと思いまして・・・

回答No.3

chukenkenkou

>int型（最小-2147483648, 最大2147483647）のauto_incrementの場合だと
>21億以上は増やせないと思うのですが、これはbigint型（最小-9372036854775808,
>最大9372036854775807）にした方がいいのでしょうか？
>まあ普通はそこまで行が増えるということはないのでしょうけど
>ログなどのテーブルで詰め込み方次第で21億以上増えないとは言い切れない

auto_incrementを使った場合、負の数は使われないのでUNSIGNED指定すれば、表現可能な最大値はさらに大きくなります。
InnoDBでは使えませんが、MyISAMなどでは、auto_incrementの列を、複合キーの2番目に指定することができます。

http://dev.mysql.com/doc/refman/4.1/ja/example-auto-increment.html

例えば、（日時,日時内通番）という構成で、日時毎に1からの通番を付けることも可能です。

商用RDBMSやPostgreSQLには、「シーケンス」という機能があります。MySQLのauto_incrementは、シーケンスに比べるとかなり簡易的な機能です。
現行システムの通番が上限に達する心配をしなければならない頃には、現行システムの見直しやMySQLの機能拡張が行われている可能性が高いと感じます。

お礼

ご返答ありがとうございます。
なるほど、複合キーというやり方は良さそうですね。
たしかにその頃にはシステムが変わってそうですね。

回答No.1

yambejp

＞user_idを主キー

主キーは、削除、挿入、更新などにつかうためにユニークであり
インデックスがはってあるキーですから、用途が微妙にことなります。

たとえば、場合によってはユーザーid自体なんらかの理由で変更
を迫られることもないとはいえません。
その際にユーザーidに依存しない独立したプライマリーキーを
もっていれば、レコード自体を引き継ぐことができます。
そのリスクを理解しているのであればユーザーidにプライマリを設定
しても構わないでしょう。

ちなみに私はユーザーidをプライマリーキーにしてしまう派です。

お礼

ご返答ありがとうございます。
なるほど、変更する可能性も考える必要があるということですね。