はじめに
[ある目的を持った人たちが仲間探しのためにプロジェクト（チーム）を作成、参加できる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";