質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.33%

  • PHP

    21289questions

    PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

  • MySQL

    6156questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

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

解決済

回答 4

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 476

workr

score 150

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)のように分けてしまい、表示時に1つにまとめるというてもありそうですがページネーションなどの実装は複雑になりそうです。

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

よろしくお願いします。


追記:

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 4

checkベストアンサー

+5

このような場合、それぞれの設計について、変更後のテーブル定義や想定されるシチュエーションの 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_iduser_idに)NOT NULL制約が使えない

という問題があります。

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


次に

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

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

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

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


最後に

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

は、

  • テーブル定義
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;


となるかと思います。

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

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

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

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


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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

+1

自分なら以下の実装にしますね。何度かこういう実装をしたことがあります。
※もしくは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みたいなイメージ

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

パッと思いつくのは、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();

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/01/13 17:07

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

    キャンセル

  • 2017/01/13 17:28

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

    キャンセル

0

何を第一義に考えるかですが、最小限の修正でということを考えた場合、既存のテーブル等を変更せずにと定義すると、以下のような方法も可能です。(追加の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;

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.33%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

同じタグがついた質問を見る

  • PHP

    21289questions

    PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

  • MySQL

    6156questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。