複数の会員種別がある会員制掲示板のデータベース設計

Question

前提・実現したいこと

会員制の掲示板サービスをlaravelで作るためにDB設計をしています。
生徒がスレッドをたてて質問し、そのスレッドに他の生徒や先生がコメントする事が出来る仕組みです。
やり取りの間にシステムからコメントが入る事もあります。
（例えば質問事項に変更があったときに「内容が更新されました」というコメントが最新コメントの次に自動で入ります）

コメントを保存するためのテーブルの設計をどうすれば良いか悩んでいます。

現時点の設計

現時点で考えているデータベース設計は以下の通りです。
生徒と先生では登録する内容が異なり認証形式も違うため別テーブルに分けています。

students テーブル

ID
パスワード
ユーザ名
学年
・
・

teachers テーブル

ID
メールアドレス
パスワード
ユーザ名
専門科目
・
・

threads テーブル

ID
立てた生徒のID (studentテーブルのIDとリレーション)
タイトル
質問内容
・
・

commentテーブルは2パターン考えています。

comments テーブルその1

ID
会員種別フラグ （boolean trueなら先生）
ユーザID
システムフラグ (boolean)
コメント
・
・

会員種別フラグとユーザIDからユーザを特定し
どちらもnullでシステムフラグがtrueならシステムのコメントとして処理します。

comments テーブルその2

ID
生徒ID
先生ID
システムフラグ (boolean)
コメント
・
・

生徒IDか先生IDどちらかがnullで無ければnullで無い方のユーザIDを用いてユーザを特定し
どちらもnullでシステムフラグがtrueならシステムのコメントとして処理します。

どちらの方がより適切なのか、また他に良い方法があれば教えて下さい。
よろしくお願いします。

Answer 1

生徒IDか先生IDどちらかがnullで無ければnullで無い方のユーザIDを用いてユーザを特定

生徒と先生の両方が入力されることがないのなら
生徒・先生ID用のカラムを１つ作ればいいのでは？

先生用のIDと生徒用のIDを完全に競合しないような範囲割をすれば
効率的に処理できますが、競合する可能性があるなら
会員種別フラグ (?)で判断してもいいと思います

Comments

[majua51]

先生の情報を別DBから流用する関係でIDは競合する可能性が高いですので
その1の案で進めてみます。ありがとうございました。

Answer 2

自分の場合、

laravel 既定の users テーブルをそのまま使い、roles テーブルで役割を分けます。

create_users_table.php

Schema::create('users', function (Blueprint $table) {
    $table->bigIncrements('id')->comment('ID');
    $table->unsignedBigInteger('role_id')->after('id'); // add
    $table->string('name');
    $table->string('email')->unique();
    $table->timestamp('email_verified_at')->nullable();
    $table->string('password');
    $table->rememberToken();
    $table->timestampe();

    $table->foreign('role_id')->references('id')->on('roles'); // add
});

create_roles_table.php

Schema::create('roles', function (Blueprint $table) {
    $table->bigIncrements('id');
    $table->string('name');
    $table->timestampe();
});

Seeder で

id: 1, name: teacher
id: 2, name: student

のように値を固定する

Userモデルを継承した Student, Teacher モデルを定義する

こうすることで、ログイン周りの機能に手を加えずに済む。

class Student extends User
{
    protected $table = 'users';

    protected static function boot()
    {
        parent::boot();

        static::addGlobalScope('student', function (Builder $builder) {
            $builder->where('role_id', 2);
        });
    }
}

class Teacher extends User
{
    protected $table = 'users';

    protected static function boot()
    {
        parent::boot();

        static::addGlobalScope('teacher', function (Builder $builder) {
            $builder->where('role_id', 1);
        });
    }
}

それぞれ、グローバルスコープを定義して、role_id = 1, role_id = 2 のユーザーに限定する処理を与える。

こうしておくことで、

Student::all() と実行しても、

SELECT * FROM `users` WHERE `role_id` = 2;

が実行されるようになる。

student, teacher に付随する情報は、それぞれ、hasOne でリレーションを定義すれば要件は満たせるはず。

Comments

[majua51]

ありがとうございます。今回は先生と生徒のテーブルを分けることが既に決まっているため利用できませんが、roleで分ける方法はもの凄く便利ですね！勉強になりました。

後学のために教えて頂きたいのですが、登録するユーザ情報の項目が異なる場合でもroleを使ってユーザ情報を一つのテーブルにした方が良いのでしょうか？ケースバイケースだとは思うのですが、何かしら指針がありましたら知りたいです。

[退会済みユーザー]

回答にその点についても書いてあります。

> student, teacher に付随する情報は、それぞれ、hasOne でリレーションを定義すれば要件は満たせるはず。

[majua51]

なるほどテーブル自体を分けてしまうのですね。ありがとうございます。