複数のテーブルで重複するフィールドがある場合のテーブル設計

Question

お世話になります。

テーブル設計で下図のようなテーブル構成があるとします。

この２つのテーブルでは同じpermissionフィールドが重複しています。例では２つですが、
これが３つ４つあるとすると、権限テーブルを１つ作成して紐付けた方がいい気がしてきました。

考えたテーブル設計は下図になります。

permissionsという全体のテーブルで共通の権限情報の管理テーブルを作成して、
必要なテーブルに紐付かせます。

問題だと感じるのはこの設計だと、テーブル数が単純に2nで増えることです。
またadmin_userテーブルには他に子テーブルがたくさんあるとすると、joinするテーブルが増えてパフォーマンスに影響が出てくる気もします。当然データ量とかにもよりますけど。

なので、ここまでする必要があるのかどうかの判断ができずに悩んでいます。

ここではあくまで例として、権限を出していますが、
特別に権限の管理として設計している訳ではないので、あくまで、
複数のテーブルに重複するフィールドがある場合の処理として考えています。

このあたりの設計例をご教授いただけるとありがたいです。
よろしくお願いいたします。

Answer 1

画面などの設計はどのようになっていますか。
例えば一覧（検索）画面などに、
ユーザの一覧と権限をすべて出す必要があるなら、
そこまでの正規化は行いません。
むしろ1テーブルに集約してしまったほうがいいかと思います。
あくまで1つの例として。

カラム名	属性	型	備考
id	PK	int	サロゲートキー
type	int	[1:admin_user/2:group]
name	varchar
permissions_r	bool
permissions_w	bool
permissions_x	bool

Comments

[annderber]

コメントありがとうございます。

すいません。私の例が悪かったです。
省略してしまっていますが、２つのテーブルが同じフィールドを持っている想定ではないです。
あくまで別テーブルとして扱えるように設計したいです。

[szk.]

他に別々のカラムも持っているということですか。

同じ意味を持つデータが他テーブルに散らばっているのは
設計的にイケてないかと。。。
yambejpさんのテーブル構成のほうがよさそうですね。

そもそもadmin_userテーブルとgroupテーブルで権限が分かれて作られた経緯など、
かなり具体的な話になってしまいます。

要件が出てくるたびに考え直すんですが、
最初に書いたように、
他の要件（画面での表示など）を容易に満たせる範囲内で正規化されるのがいいと思います。

Answer 2

ぱっとみおもったこと。詳細がわからないので間違っていたらすみません。

userテーブルと、permissonとパーミッションテーブルがあり
紐付けようにuser_permissonテーブルがある状態ですよね？

• 前もお伝えしたかと思うのですが、さほど拡張性の必要がないなら

user_permissionテーブルは

userid	insert	update	delete
int	bool	bool	bool

というもたせ方のほうがよいかもしれません。
（※boolはtinyint not nullでもOK）

• とくにuserテーブルはuserid(int)とusername(varchar)の2つ項目をもつこと

(useridはvarcharでも可)

• user_permissionテーブルはidをプライマリにした上で

useridとpermissioncodeで複合ユニーク属性にすること

• permissionをグルーピングするならpermissionテーブルに各

パーミッションの親groupidを保持して、groupテーブルとリレーションすること

Comments

[annderber]

コメントありがとうございます。

おっしゃる通りのテーブル構成です。
確かに前回した質問と似通ってしまっていますね。今回は複数のテーブルに跨がるチェックボックスの場合ということですが、これも変更率が低いならそれぞれテーブルに持ってしまっていいですかね。

少し質問の内容と異なりますが、例えばuserテーブルに紐付く「コース」があったとして、
そのコースには値段とかの情報があるので、別テーブルとして作成する。
そしてコースはチェックボックスで複数選択可能としたら、リレーションを張るためにもuser_courseなどの子テーブルを作成する必要がでてきますよね。

追記
yambejpさんの設計例を少し読み間違えていました。
>permissionをグルーピングするならpermissionテーブルに各
>パーミッションの親groupidを保持して、groupテーブルとリレーションすること
というのはpermissionテーブルが必要なテーブルすべてに共通のPKを生成するテーブルを１つ設けるということですか？