MySQLで２種類のユーザーが記事を書く場合の設計

Question

MySQLを使ったウェブサイトで、管理ユーザー(admins)がブログ記事（articles）を書ける仕組みになっていて、articles.author_id と admins.id を紐付けて著者を表示しています。

機能追加で一般ユーザー(users)からの記事投稿も受け付けるようにすることになったため、設計を変更しようと考えています。

admins と users は構造が異なり、id も重複しています。

最小限の変更で実装してしまうなら articles.admin_id, articles.user_id というカラムを用意し、管理者が書いた記事には admin_id = 管理者ID, user_id = null にし、ユーザーが書いた記事には user_id = ユーザーID, admin_id = null にするという手段があるかと思いますが綺麗とは言えません。

あるいは article_admin, article_user などの中間テーブルを用意して管理者IDと記事、ユーザーIDと記事を紐付けるのも良さそうですがどのように JOIN するかがイメージできません。

思い切って公式記事(official_articles)、ユーザー記事(user_articles)のように分けてしまい、表示時に１つにまとめるというてもありそうですがページネーションなどの実装は複雑になりそうです。

このようなケースではどういった設計を取るべきなのでしょうか？
参考ですがフレームワークとして Laravel 5.2 を利用しています。

よろしくお願いします。

---

追記:

記事を一覧表示する際は同じページに作成者が混在した状態で日付順に表示されます。

Answer 1

このような場合、それぞれの設計について、変更後のテーブル定義や想定されるシチュエーションの SQL を実際に書いてみることをお勧めします。
そうすれば、修正の難易度がより具体的にイメージできたり、それまで気づかなかった問題点が見えてくることもあるからです。

と、言うわけで

記事を一覧表示する際は同じページに作成者が混在した状態で日付順に表示

を例に取ると、

articles.admin_id, articles.user_id というカラムを用意し、管理者が書いた記事には admin_id = 管理者ID, user_id = null にし、ユーザーが書いた記事には user_id = ユーザーID, admin_id = null にする

は

• テーブル定義

CREATE TABLE admins (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(64) NOT NULL
);

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

CREATE TABLE articles (
  id INT PRIMARY KEY AUTO_INCREMENT,
  article TEXT NOT NULL,
  create_date DATETIME NOT NULL,
  admin_id INT NULL,
  user_id INT NULL,

  CONSTRAINT FOREIGN KEY (admin_id) REFERENCES admins (id),
  CONSTRAINT FOREIGN KEY (user_id) REFERENCES users (id)
);

• SQL

SELECT ar.id, ar.article, ar.create_date, ad.name, u.name
FROM articles AS ar
LEFT OUTER JOIN admins AS ad ON ar.admin_id = ad.id
LEFT OUTER JOIN users AS u ON ar.user_id = u.id
ORDER BY create_date;

のようになるかと思います。

比較的シンプルな SQL が書ける上に、
（ご質問のウェブサイトの詳細を知らないため確かなことは言えませんが、）テーブル定義の変更による影響が最も少ない方法だと感じます。

しかし、icchii様も言及されている通り、この設計には

（articlesテーブルのadmin_idとuser_idに）NOT NULL制約が使えない

という問題があります。

もう少し具体的に言うと、
「admin_idとuser_idの両方が NULL、または両方に値が入ってしまう」
というデータ不整合を DB の機能で防止できないため、それをアプリケーション側で対応する必要がある、ということです。

---

次に

article_admin, article_user などの中間テーブルを用意して管理者IDと記事、ユーザーIDと記事を紐付ける

については、どのような設計を想定されているのか私にはイメージが付かなかったので、割愛させていただきます（笑

しかし、中間テーブルを設ける場合、
「すでに存在するarticlesテーブルとadminsテーブルの紐付けを、どのように付け替えるか」
という、リリース手順の問題が生じる可能性があります。

既存の記事の投稿数や「リリース時にどの程度の時間、サービスを停止できるか」といった制限などを勘案した上で、
リリース時の具体的な作業手順（※）をイメージしてみてください。
※「どのような ALTER TABLE 文や UPDATE 文を実行すれば良いのか」といったレベルで

---

最後に

公式記事(official_articles)、ユーザー記事(user_articles)のように分けてしまい、表示時に１つにまとめる

は、

• テーブル定義

CREATE TABLE admins (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(64) NOT NULL
);

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

CREATE TABLE official_articles (
  id INT PRIMARY KEY AUTO_INCREMENT,
  article TEXT NOT NULL,
  create_date DATETIME NOT NULL,
  author_id INT NOT NULL,

  CONSTRAINT FOREIGN KEY (author_id) REFERENCES admins (id)
);

CREATE TABLE user_articles (
  id INT PRIMARY KEY AUTO_INCREMENT,
  article TEXT NOT NULL,
  create_date DATETIME NOT NULL,
  author_id INT NOT NULL,

  CONSTRAINT FOREIGN KEY (author_id) REFERENCES users (id)
);

• SQL

SELECT *
FROM (
  (
    SELECT oa.id, oa.article, oa.create_date, a.name
    FROM official_articles AS oa
    INNER JOIN admins AS a ON oa.author_id = a.id
  )
  UNION ALL
  (
    SELECT ua.id, ua.article, ua.create_date, u.name
    FROM user_articles AS ua
    INNER JOIN users AS u ON ua.author_id = u.id
  )
) AS tmp
ORDER BY tmp.create_date;

となるかと思います。

「既存のテーブル（adminsとofficial_articles）を変更しなくてよい」
というメリットがある反面、一覧表示の SQL には

ページネーションなどの実装は複雑になりそう

というご懸念の他に、記事の投稿数が増えると性能問題を引き起こす可能性があります。

なぜなら、MySQL では DERIVEDテーブル(※)を ORDER BY する際にインデックスが使用できないからです。
※ FROM (サブクエリ) 部分で生成される一時テーブルのこと

---

個人的には最初の設計案が最も妥当な落としどころだと感じますが、
記事データの（ご質問で言及されていない）他の用途などによっては別の案の方が相応しい、ということもあり得ます。

上のような考え方を参考に、最終的な判断はお任せします（笑

Answer 2

自分なら以下の実装にしますね。何度かこういう実装をしたことがあります。
※もしくはadminsとusersのテーブルを統合するか...（影響範囲しだいですが）

最小限の変更で実装してしまうなら articles.admin_id, articles.user_id というカラムを用意し、管理者が書いた記事には admin_id = 管理者ID, user_id = null にし、ユーザーが書いた記事には user_id = ユーザーID, admin_id = null にするという手段があるかと思いますが綺麗とは言えません。

JOINもシンプルですし、構造上わかりやすいので、そんなに悪いとは思いませんけどね。
表示のときには、管理者か一般ユーザか存在する方を出せばよいだけですし。
唯一のデメリットはNOT NULL制約が使えないことぐらいかと（アプリ側でカバー）

---

もしくは、もし今後管理者と一般ユーザ両方に紐付ける必要がテーブルが増えるなら、authorsという、adminsとusers両方を管理する中間テーブルを作ってauthorsとarticlesを関連付けるのもありかもしれません。

authors (id, admin_id, user_id)
admins.id ⇔ authors.admin_id authors.id ⇔ articles.author_id
users.id ⇔ authors.user_id authors.id ⇔ articles.author_id

※adminsとusersの基底クラスauthorsみたいなイメージ

Answer 3

何を第一義に考えるかですが、最小限の修正でということを考えた場合、既存のテーブル等を変更せずにと定義すると、以下のような方法も可能です。（追加のviewと追加のuser用記事テーブル、列挙用のコマンドを置き換え）

条件としては、「admins と users は構造が異なり、id も重複しています。」を考慮したものです。

adminsとusersそれぞれのテーブルで共通に参照したい項目が著者名（name）だけだとして、
以下のような感じでviewを作成してしまいます。

create view all_users as select id,name,0 as type from admins union select id,name,1 from users;

さらに、追加でuser用の記事を格納するテーブル（user_articles）を作成します。これは、基本的に今までのテーブル構造と同じで良いでしょう。（以下のコマンドは同じものと想定して記載しています）
後は、以下のようなコマンドで一覧が取得できます。記事の作成順に取得したければ、order byで、articlesに含まれる投稿日などを指定すれば良いでしょう。

select * from all_user join (select *,0 as type from articles union select *,1 from user_articles) as acl on all_user.id=acl.author_id and all_user.type = acl.type;

Answer 4

パッと思いつくのは、article_adminで管理者ユーザだけを持たせておくテーブルを作ることですかね。
articlesのauthor_idとarticle_adminでleft join がわかりやすいかもです。
管理者が書いた記事だけを抽出するSQL例

Select * from articles left outer join article_admin on author = article_admin
where article_admin.id is not null

ちなみに、Laravelのleft joinの例です(すいません。未検証ですが)

$articles = DB::table('articles')
            ->leftJoin('article_admin', 'articles.author ', '=', 'article_admin.article_admin')
            ->whereNotNull('article_admin.article_admin');
            ->get();

Comments

[workr]

記事一覧を表示する際は作成者が混在した状態で日付順に表示しようと思っています。
記事作成者が user か admin かによって join するテーブルを切り替えるようなイメージなのですがどうでしょうか？

[motuo]

単純に管理者かどうかを見極めたいだけであれば、where句を消してarticle_adminがnullかどうかで処理を分ければよいと思います。
もし、それ以外に情報を付加したいのであれば、article_userを作れってleftjoinを追加すればよい気がします。