「1ユーザごとに、1つのデフォルトフォルダ」という機能の実装に適切なテーブル設計について

Question

現状・知りたいこと

WEBサイトに以下の機能を実装中です。

➀ ユーザがアイテムを「フォルダへ保存」できる
➁「フォルダへ保存」するとデフォルトフォルダに入り、フォルダ間でアイテムを移動できる
➂ フォルダはユーザが任意に作成でき、デフォルトフォルダは任意に変更できる

そして「1ユーザごとに、1つのデフォルトフォルダ」と制約したいのですが、そのための適切な方法を知りたいです。

発生している問題

以下のようにCREATE TABLEしましたが、「1ユーザごとに、1つのデフォルトフォルダ」が制約できません。
つまり以下ではis_default=trueのレコードを、1ユーザが複数持ててしまいました。

CREATE TABLE users (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE folders (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    is_default BOOLEAN DEFAULT false,
    CONSTRAINT `folders__foreign__user_id` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
);

自分なりの案

いくつか案を考えてみましたが、このどれが適切なのか？それとも他に適切な方法があるのか？がわかりません。

案1

folders.is_default というカラムは削除し、default_foldersというリレーションテーブルを作り、以下のようにする案です。

CREATE TABLE default_folders (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    folder_id INT UNSIGNED NOT NULL,
    UNIQUE `default_folders__unique__user_id` (`user_id`),
    CONSTRAINT `default_folders__foreign__user_id` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`),
    CONSTRAINT `default_folders__foreign__folder_id` FOREIGN KEY (`folder_id`) REFERENCES `folders`(`id`)
);

しかしわざわざ別テーブルを作ることが適切なのか？判断できません。
「なんかテーブルって少ない方が良さそうだよなぁ」という印象があります。

案2

folders.is_default というカラムは削除し、usersテーブルにdefault_folder_idカラムを設け、以下のようにする案です。

CREATE TABLE users (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    default_folder_id INT UNSIGNED NOT NULL,
    -- しかし以下の外部キー制約を課すとジレンマが生じる
    CONSTRAINT `users__foreign__default_folder_id` FOREIGN KEY (`default_folder_id`) REFERENCES `folders`(`id`)
);

しかし上のコメントにあるように、users__foreign__default_folder_idという外部キー制約を課すと、以下ABというジレンマが生じてしまいます。

A: usersへのINSERTの前に、foldersへのINSERTが必要 (users__foreign__default_folder_idのため)
B: foldersへのINSERTの前に、usersへのINSERTが必要 (folders__foreign__user_idのため)

このジレンマの解消は、INSERTのときはデータベースになんからのロックをかけて、次のPKを取得するという方法で回避できますが、そういう方法が適切なのか？判断できません。
「なんかロックって遅そうだよなぁ」という印象があります。

まとめ

以上の2案についてどちらが適切か、または他にオススメの案がございましたら教えて頂けませんでしょうか？

適切さは全体の仕様次第でしょうけれど、WEBサイト制作経験がなくまるで判断できない状況です。

「まぁ私ならこうするな」「普通だったらこうだよ」「こうするとこういうとき便利だよ」など、経験豊富な皆様からのご意見を頂戴し、判断の指針にさせて頂ければと思っています。

バージョン

php 👉 8.2
MySQL 👉 5.7

よろしくお願い致します。

Comments

[winterboum]

user_id と is_default の複合 indexを作ってユニーク制約したら？

[munekun]

質問がわかりにくくて申し訳ございません。
「1ユーザごとに、1つのフォルダと1つのデフォルトフォルダ」という制約ではありません。
「1ユーザごとに、1つのデフォルトフォルダ」という制約です。

folders テーブルに (user_id, is_default) で複合 index を貼ると、is_default=false なレコードをそのユーザは1つしか INSERT できず、➂にある任意の作成が達成できません。

is_default=true のレコードは1つだけですが、is_default=false のレコードは任意に作成できるようにしたいのです。

[tezcello]

精読していないけれど...
フォルダの名前と、実際の保存場所は一致しなければいけない理由は無いので、「デフォルトフォルダ」の名前はdefaultと固定してしまえば良いのでは？
＿＿そもそも実際に存在するフォルダ（ディレクトリ）に保存しなければいけないとは限らない
ユーザのIDとフォルダ名の組み合わせでユニークになれば良いのだろうと思ったり...

あぁ、デフォルトフラグ（is_default BOOLEAN DEFAULT false）ではなく、ファイル識別子（例えば file_identifier VARCHAR(255) ）にして、デフォルトフォルダはユーザIDと特定文字列（例えば 'default'）を連結したモノを収め、通常フォルダは NULL とか？

[munekun]

ありがとうございます。なるほどー！さすが、巧いこと工夫されますね。外部キーにしつつジレンマも解消でき、しかも楽です。

Answer 1

つまり以下ではis_default=trueのレコードを、1ユーザが複数持ててしまいました。

問題ないですよ、プログラム側で制御しましょう。
DBによる制約が正となるよう設計は一昔前の考え方です。プログラム組むのに自信が無いからDBの制約に頼りたいなど強い理由があれば別ですが、そこまで考えて無いのならプログラム側で制約掛ければ十分です。

Comments

[sazi]

> DBによる制約が正となるよう設計は一昔前の考え方です。
どういったことで、ひと昔前なのでしょうか？

制約を設ける事で、プログラムのバグなどからデータを保全することが出来ますが、そういった事はどのような理由で古い考え方になったのでしょうか？

[tezcello]

データベースは単なるデータストレージと考えているって事でしょう。
それが最近の世の中の流れなのか、個人的な嗜好なのかはしりません...

僕は、何でもかんでもプログラムでやるってのは、好きではないですが。

[munekun]

hentaiman様、ありがとうございます。そういう実装でも平気だったのですね。安心できました。

Answer 2

案１が良いのでは無いかと思います。
理由としては、下記要件の実装がシンプルなものになると思われるからです。

➁「フォルダへ保存」するとデフォルトフォルダに入り、フォルダ間でアイテムを移動できる
➂ フォルダはユーザが任意に作成でき、デフォルトフォルダは任意に変更できる

Comments

[munekun]

ありがとうございます。たしかにあっちこっちで書きやすいし分かりやすくなりそうです。

でもいざ書いてみると、phpで以下のように「createUser() のトランザクション内で、cerateDefaultFolder() のトランザクション」になってしまいうまくいきませんでした。

```php
// ユーザ作成
fuunction createUser()
{
// トランザクション開始
// users テーブルへの保存処理
// cerateDefaultFolder($userId)
// トランザクション終了
}

// デフォルトフォルダ作成
function cerateDefaultFolder($userId)
{
// トランザクション開始
// folders テーブルへの保存
// default_folders テーブルへの保存
// トランザクション終了
}
```

[sazi]

デフォルトフォルダはユーザーが任意に設定できるのですよね？
そもそも案１であれば、マッピングのテーブルとして個別に存在している訳ですし、
ユーザー作成とは別のトランザクションの方が効率的なのではないでしょうか

[munekun]

たびたびのアドバイスありがとうございます。

ユーザは必ずデフォルトフォルダを１つだけ持ってほしい。という考えで、ユーザ作成のトランザクションの中に、デフォルトフォルダ作成も押し込めた次第です。（ユーザ作成に失敗したり、デフォルトフォルダ作成に失敗したら、双方の作成はロールバックしたいという考えです。）

ユーザによる任意の設定の際は別途、デフォルトフォルダ変更を担うメソッドを使うつもりです。

[sazi]

「ユーザー」の属性に”デフォルトフォルダ"を設定するには、先に「フォルダー」が存在している必要があるので、「卵が先か鶏が先か」という事になります。
エンティティとして、「ユーザー」と「フォルダー」を独立させると、「デフォルトフォルダ」をマッピングとするというのはごく自然だし、実装もシンプルになります。

質問があるべき論のような内容でしたので、正規化寄りの回答となっていますが、目的に対して冗長と判断するのであれば正規化を緩める事もありだとは思います。

[munekun]

仰る通りな気がしてきました。「なんかテーブルって少ない方が良さそうだよなぁ」という印象に引っ張られていましたけれど、UIとの兼ね合いや拡張性など全体を見直しているうちに、おっしゃる通り案1の方針がいろいろシンプルに実装できそうな筋道が見えてきました。
ナマイキな口ごたえしてすみませんでした笑

Answer 3

ということですと。。。
案１ と 案２ では 制約条件が異なります。
案１では案２の default_folder_id INT UNSIGNED NOT NULL の NOT NULL に当たる制約はないです。
それで良いのなら
案２で
default_folder_id INT UNSIGNED NOT NULL の NOT NULL を付けず
CONSTRAINT users__foreign__default_folder_id に ON DELETE SET NULL（でしたっけ）を付けて default_folder_id なしを許したらどうでしょう

Comments

[munekun]

たしかに案1では必ず1つのデフォルトフォルダを持つような制約になっていませんでしたね。欲を言えばそうしたい気持ちですが、でもご提示の方法とあとはトランザクションで対処いたします。まさかNULLにも外部キーの値にもできる方法があったなんて、今回の件にぴったりですね。ありがとうございます。

[winterboum]

＞NULLにも外部キー
はやったことがないので、うまく行かなかったら書式とか確認してください

[munekun]

あ、いえ、「NULLにも外部キーの値にもできる」と申し上げたのは、「ON DELETE SET NULL を指定したカラムは、NULL を入れることもできるし、もし NULL 以外の値を入れればそれは外部キーとしてのチェックもしてくれる」という意味でした。

ON DELETE SET NULL を指定し、まず NULL で INSERT し、直後に外部キーの値を入れて UPDATE という方法で、ジレンマをうまく回避して実装できました。

改めてありがとうございます。