Help us understand the problem. What is going on with this article?

フリマアプリの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 認証」

suzy1031
駆け出しエンジニア。ruby rails jQuery AWS Java React
https://suzyanamateurengineer.hatenablog.com/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away