データベース設計（複合主キー）をどう設定する、またはしないどちらがベストでしょうか？

Question

テーブル設計

kawaii_user_id	not_kawaii_user_id	company_id
ForeignKey-> UserTable	ForeignKey-> UserTable	ForeignKey-> companyTable

上記のような、「あるUser」が「あるUser」を持つことが出来るテーブルがあります。
また、Userが属するCompanyテーブルがあります。
User間の関係を持てるのは同一Companyのみです。

今回のテーブルでは、userAがkawaii_user_idでUserBをnot_kawaii_user_idとしていても、userBがkawaii_user_idとしてuserAをnot_kawaii_user_idという状態を同時に持つことが可能ということを想定しています。

現状

最初の設計として、このユーザ間の関係性を一意に保つためにkawaii_userとnot_kawaii_userを複合主キーとして設計しました。

しかし、最初に気付いたのは上記の複合主キーであると、属する会社に関係しない一意性になってしまいます。
(CompanyAでUserA→UserBの関係を持つと、CompanyBではUserA→UserBの関係が持てない。)

そのため、複合主キーをkawaii_user_id、not_kawaii_user_id、company_idの3つ設定すれば、回避出来ると考えたのですが、今度は

今回のテーブルでは、userAがkawaii_user_idでUserBをnot_kawaii_user_idとしていても、userBがkawaii_user_idとしてuserAをnot_kawaii_user_idという状態を同時に持つことが可能ということを想定しています。

という、UserA→UserBとなっていてもUserB→UserAという関係を持てるという関係性を保つ事が厳しくなってしまいました。

こういった場合、どういう制約の掛け方をするのがベストでしょうか？
よろしくお願いします。

Answer 1

用途がよくわかりませんが、「可愛い」か「可愛くない」かはキーではなくフラグ(kind or status)としてのカラムにするべきではないでしょうか？
質問のテーブル？とUserTable, companyTable でどんなSQLが必要になるのか、CREATE TABLE, CREATE INDEX を添えて質問に提示されては？

Comments

[tomuziso]

回答ありがとうございます。すみません自己解決しました・・・。

ちなみに質問用にカラム名を変えたのですがあまり適切ではありませんでした。
フラグを持たせるというのはUser側に、ということで合ってますか？
「可愛い」「可愛くない」等の固定の状態を持たせるとして、UserAの状態はUserBから見て「可愛い」UserCからみて「可愛くない」などの変動する状態って実現出来るんでしょうか・・・？

[Orlofsky]

カラムの用途を想像できるネーミング能力は重要です。
USERID, AITEID をPRIMARY KEY とする「可愛い」「可愛くない」情報を持つテーブルにを用意すれば相手によって可愛いさを変えることができます。

[tomuziso]

ありがとうございます。
ネーミング能力重要ですよね・・・。テーブル設計する時にはいつも悩みます。
質問する時にも分かりやすい名前で出せるようにします。

Userとは別テーブルでふるまいの状態を持つテーブルを作成すれば確かに実現出来そうです！
ありがとうございました！

Answer 2

すみません、お風呂入ってよく考えてみたら自己解決しました。
そもそもの前提としてcompanyが違うものの、userIdが同じ、ということはありえませんね・・・

また、複合主キーとして、userA→userBとuserB→userAは別物として扱われるので質問にあげた前提がそもそも間違えでした。

Comments

[sazi]

useridが社員番号だとすると、companyが違っても同じ値はあり得ますね。
useridが個人番号なら重複はしませんが、複数の企業に所属する場合（世の中には当然存在する）、やはりcompanyと組み合わせないと一意になりません。
サロゲートキーを一意キーにすると、それらの一意性の管理は必要になる場面まで先送りできます。

[tomuziso]

コメントありがとうございます。
確かに社員番号と考えるなら重複も想定出来ますね・・・。
今回はUserテーブルのidがそのまま社員番号＝個人番号という想定なので重複はない、という条件でした。

サロゲートキーを一意キーにする、という事は考えていませんでした。
確かにuserIdが社員番号でcompanyと紐づくと考えると、サロゲートキーを一意キーにすることで重複することは避けられそうですね。

ありがとうございました！