タグ機能を持つユーザー-プロジェクトのSNSのDB設計

#はじめに
[ある目的を持った人たちが仲間探しのためにプロジェクト（チーム）を作成、参加できるSNS]のプロトタイプ作成でチームで考案したデータベース設計を備忘録として残しておく。
プロトタイプであるため最低限必要なデータのみで構成した。

#概要
ユーザーとプロジェクトが存在し、それぞれ関連するタグ付けを行うことができ、ユーザーはプロジェクトに参加申請したり、作成することができる。

##仕様
・ユーザーログイン機能
・タグ機能　-> ユーザー、プロジェクトに関連するタグ付けを行うことができる（Qiitaのタグ付け機能に近い）
・ユーザープロフィール機能（紹介文、タグ）
・プロジェクトへの参加申請、承認機能

##データベース
###MySQL
![image.png](https://qiita-image-store.s3.amazonaws.com/0/262578/e6ee7415-0a68-7dc7-0a74-3d912ea02d75.png)

#基礎要素
##エンティティ
- ユーザー
- プロジェクト
- タグ

![スクリーンショット 2018-09-19 16.50.50.png](https://qiita-image-store.s3.amazonaws.com/0/262578/91bdd76c-3fc4-82d3-e9b3-2a76b38dfa72.png)

##属性
![スクリーンショット 2018-09-19 16.50.57.png](https://qiita-image-store.s3.amazonaws.com/0/262578/69e3b4c9-bf91-0f9b-d51b-5126b5015623.png)


##関連(リレーション)、関連の多重度
![スクリーンショット 2018-09-19 16.51.03.png](https://qiita-image-store.s3.amazonaws.com/0/262578/8284e0c3-0dc1-b535-c747-ebbb4a86f0df.png)

エンティティ同士の関連性はユーザー、プロジェクト、タグそれぞれすべて多対多の関係であるため、それぞれの関連性について中間テーブルを用意することで解決する。

####【中間テーブルを利用した関連】
![スクリーンショット 2018-09-19 16.51.09.png](https://qiita-image-store.s3.amazonaws.com/0/262578/dc1d44e4-4097-8129-4b8b-79e571336da0.png)


#テーブル設計
##【ユーザ情報table】 users


| __user_id__   　  |user_name       |mail    |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のユーザーが参加しているプロジェクト一覧

```sql
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のユーザーにつけられているタグ一覧

```sql
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";
```

#参考サイト
- [決して陳腐化しないデータベース設計の超基礎](https://www.ulsystems.co.jp/topics/006)
- [4ステップで作成する、DB論理設計の手順とチェックポイントまとめ](https://qiita.com/nishina555/items/a79ece1b54faf7240fac)
- [若手プログラマー必読！５分で理解できるER図の書き方５ステップ](https://it-koala.com/entity-relationship-diagram-1897)
- [draw.io (会員登録不要で利用できるドローイングツール)](https://www.draw.io/)