はじめに
[ある目的を持った人たちが仲間探しのためにプロジェクト（チーム）を作成、参加できるSNS]のプロトタイプ作成でチームで考案したデータベース設計を備忘録として残しておく。
プロトタイプであるため最低限必要なデータのみで構成した。
概要
ユーザーとプロジェクトが存在し、それぞれ関連するタグ付けを行うことができ、ユーザーはプロジェクトに参加申請したり、作成することができる。
仕様
・ユーザーログイン機能
・タグ機能 -> ユーザー、プロジェクトに関連するタグ付けを行うことができる（Qiitaのタグ付け機能に近い）
・ユーザープロフィール機能（紹介文、タグ）
・プロジェクトへの参加申請、承認機能
データベース
MySQL
基礎要素
エンティティ
- ユーザー
- プロジェクト
- タグ
属性
関連(リレーション)、関連の多重度
エンティティ同士の関連性はユーザー、プロジェクト、タグそれぞれすべて多対多の関係であるため、それぞれの関連性について中間テーブルを用意することで解決する。
【中間テーブルを利用した関連】
テーブル設計
【ユーザ情報table】 users
| user_id | user_name | introduction | password | github_URL | |
|---|---|---|---|---|---|
| ユーザーID | ユーザー名 | メールアドレス | 紹介文 | パスワード | GithubのプロフィールURL |
【プロジェクト情報テーブル】 projects
| project_id |project_name |admin_user_id |introduction|private|
|:-----------------|------------------:|:------------------:|:------------------:|:------------------:|:------------------:|
| プロジェクトID| プロジェクト名 |管理者のユーザーID |紹介文|公開設定|
【ユーザーとプロジェクトの関連性テーブル】 user_project
| user_project_id |user_id |project_id |state|
|:-----------------|------------------:|:------------------:|:------------------:|:------------------:|
| プロジェクトとユーザーの関連性を表すID | ユーザーのID |プロジェクトID |ユーザーとプロジェクトの関係を表す（申請中、承認済み、拒否済み）|
【タグ情報テーブル】 tags
| tag_id | tag_name |
|---|---|
| タグのID | タグの名前 |
【タグとユーザー、プロジェクトの関連性テーブル】 tag_user_project
| tag_user_project_id |tag_id |target_id |target_type|
|:-----------------|------------------:|:------------------:|:------------------:|:------------------:|
| タグとユーザーまたはプロジェクトの関連性を表すID | タグのID |ターゲット（ユーザーorプロジェクト）のID |ターゲットの種類(ユーザーorプロジェクト)|
【参考】クエリ
ユーザーが属しているプロジェクトの名前を出力するクエリ
例)user_id = 1のユーザーが参加しているプロジェクト一覧
SELECT project_name
FROM projects
LEFT JOIN user_project ON projects.project_id = user_project.project_id
WHERE user_id = "1" AND user_project.state = "approved";
※state="approved"はユーザーがプロジェクト参加申請が承認済みであることを示す
ユーザーにつけられているタグを出力するクエリ
例)user_id = 1のユーザーにつけられているタグ一覧
SELECT tag_name
FROM tag_user_project
LEFT JOIN tags ON tags.tag_id = tag_user_project.tag_id
WHERE target_id = "1" AND target_type = "user_id";