【DB】フリマアプリのDB設計を考える

## 最終課題
**自分用メモです**
## WHAT
DB設計をREADMEに書く前にmarkdownで書く
## WHY
チームメンバーと共有する為

# DB設計
## usersテーブル

|Column|Type|Options|
|------|----|-------|
|nickname|string¥null: false|
|family_name|string|null: false|
|fairst_name|string|null: false|
|family_name_kana|string|null: false|
|first_name|string|null: false|
|birth_year|string|null: false|
|birth_month|string|null: false|
|birth_day|string|null: false|
|password|string|null: false|
|email|string|null: false, unique: true|
|status|integer|null: false|
|deleted_at|datetime|null: false|

### Association
- has_many :items
- has_many :likes dependent: :destroy
- has_many :comments dependent: :destroy
- has many :messages dependent: :destroy
- has many :points dependent: :destroy
- has_many :social_providers dependent: :destroy
- has_one :deliver_adresses dependent: :destroy
- has_many :wallets dependent: :destroy
- has_many :reviews dependent: :destroy
- has_many :orders dependent: :destroy

## social_providersテーブル

|Column|Type|Options|
|------|----|-------|
|user_id|references|null: false, FK: true|
|provider|string|null :false|

### Association
- belongs_to :user

## deliver_adressesテーブル

|Column|Type|Options|
|------|----|-------|
|user_id|references|null: false, FK: true|
|family_name|string|null: false|
|first_name|string|null: false|
|family_name_kana|string|null: false|
|first_name_kana|string|null: false|
|zip_code|integer|null: false|
|prefecture|string|null: false|
|city|string|null: false|
|adress1|string|null: false|
|adress2|string|
|telephone|string|unique: true|

### Association
- belongs_to :user

## walletsテーブル

|Column|Type|Options|
|------|----|-------|
|user_id|references|null: false, FK: true|
|money|integer|null: false|

### Association
- belongs_to :user

## pointsテーブル

|Column|Type|Options|
|------|----|-------|
|user_id|rederences|null: false, FK: true|
|score|imteger|null: false|

### Association
- belongs_to :user

## messagesテーブル
|Column|Type|Options|
|------|----|-------|
|user_id|references|null: false, FK: ture|
| item_id | references | null: false, FK: true |
| text    | text       | null: false           |

Association

- belongs_to :user
- belongs_to :item

## commentsテーブル

| Column  | Type       | Options               |
| ------- | ---------- | --------------------- |
| user_id | references | null: false, FK: true |
| item_id | references | null: false, FK: true |
| text    | text       | null: false           |
|         |            |                       |

Association

- belongs_to :user
- belongs_to :item

## likesテーブル

| Column  | Type       | Options              |
| ------- | ---------- | -------------------- |
| user_id | references | null:false, FK: true |
| item_id | references | null:false, FK: true |
|         |            |                      |

Association

- belongs_to :user
- belongs_to :item

## reviewsテーブル

| Column  | Type       | Options               |
| ------- | ---------- | --------------------- |
| user_id | references | null: false, FK: true |
| item_id | references | null: false, FK:true  |
| rate    | integer    | null: false           |
| text    | text       |                       |
|         |            |                       |

Association

- belongs_to :item
- belongs_to :user

## ordersテーブル

| Column        | Type       | Options               |
| ------------- | ---------- | --------------------- |
| buyer_user_id | references | null: false, FK: true |
| item_id       | references | null: false, FK: true |
|               |            |                       |

Association

- belongs_to :user
- belongs_to :item

## itemsテーブル

| Column         | Type       | Options               |
| -------------- | ---------- | --------------------- |
| category_id    | references | null: false, FK: true |
| shipping_id    | references | null: false. FK: true |
| brand_id       | references | null: false. FK: true |
| seller_user_id | references | null: false, FK: true |
| name           | string     | null: false           |
| text           | text       | null: false           |
| condition      | integer    | null: false           |
| price          | integer    | null: false           |
| trading_status | integer    | null: false           |
| completed_at   | datetime   |                       |
|                |            |                       |


### Association
- belongs_to :user
- belongs_to :category
- belongs_to :brand
- has_many :likes dependent: :destroy
- has_many :comments dependent: :destroy
- has_many :messages dependent: :destroy
- has_one :shipping
- has_many :reviews
- has_many :item_images dependent: :destroy
- has_one :order

## item_imagesテーブル
|Column|Type|Options|
|------|----|-------|
|item_id|references|null: false, FK: true|
|image_url|string|null: false|

### Association
- belongs_to :item


## categoriesテーブル（経路列挙モデル）

| Column | type   | Option      |
| ------ | ------ | ----------- |
| path   | text   | null: false |
| name   | string | null: false |
|ancestry|string  |             |

### Association
- has_many :brands
- has_many :items
- has_one :category_size
- has_one :size, through: :category_size
- has_one :category_brand_group
- has_one :brand_group, through: :category_brand_group
- has_ancestry

## sizesモデル

| Column      | Type       | Options               |
| ----------- | ---------- | --------------------- |
|path|text|null: false|
|kind|string|null: false|
|ancestry|string|

### Association
- has_many :items
- has_many :category_sizes
- has_many :categories, through: :category_sizes
- has_ancestry

## category_sizesテーブル
| Column      | Type       | Options               |
| ----------- | ---------- | --------------------- |
|category_id|references|FK: ture|
|size_id|references|FK: true|

### Association
- belongs_to :category
- belongs_to :size

## brandsテーブル

| Column      | Type       | Options               |
| ----------- | ---------- | --------------------- |
| category_id | references | null: false, FK: true |
| brand_id    | references | null: false, FK: true |
| name        | string     | null: false           |
|             |            |                       |
 
### Association
- has_many :item
- belongs_to :brand_group
- belongs_to :category

## brand-groupsテーブル

| Column | Type   | Options     |
| ------ | ------ | ----------- |
| name   | string | null: false |
|        |        |             |

Association
- has_many :brands

## shippingsテーブル

| Column        | Type    | Options     |
| ------------- | ------- | ----------- |
| fee_burden    | boolean | null: false |
| service       | integer | null: false |
| area          | integer | null: false |
| handling_time | integer | null: false |
|               |         |             |


### Association
- belongs_to :item

### cards(クレジットカード)
**pay.jpで実装するので不要**


### 参考
https://tech.mercari.com/entry/2017/11/29/161124
→データ容量が増え続けるから商品テーブルのdescriptionをテーブルから切り離す。将来の拡張性を考えDB設計を考えると別テーブルを作成した方がエンジニアチックでカッコいい
https://teratail.com/questions/171228
https://komiyak.hatenablog.jp/entry/20141103/1415016929
→googleなど外部の認証機能を使う「OAuth 認証」