[Rails] DB design for EC site

Introduction

I created an EC site using Ruby on Rails (-v 5.2). This time, I will write that I did something like this about the DB design at that time.

Referenced site

A must read for young programmers! How to draw an ER diagram that can be understood in 5 minutes 5 steps

I referred to "Checking the system scenario" on the above site.

As follows.

Use case description

--Use case name: Customer orders a product --Overview: Order products on the EC site --Actor: Customer --Preliminary conditions: You are logged in --Trigger: Put the product in the cart and press the "Order procedure" button on the cart screen. --Basic flow --Enter the shipping address and payment method on the order screen --Press the "Confirm Order Details" button --Check the order details and press the "Confirm Order" button to complete the purchase. --Remarks: When pressing "Confirm order details", you will not be able to press the "Confirm order details" button unless you have entered it.

Identifying entities

Master system or transaction system

--Master system

I was thinking about this in the early stages, but since I started creating it, I have changed it as appropriate. The payment method is also credit card (using PAY.JP) only.

DB design of the main subject

I was worried about what to do with the cart table for the cart function, which is an essential function on the EC site, and the order details table, but I dropped it as an intermediate table with the following feeling.

スクリーンショット 2020-06-01 23.28.25.png

As for the cart table, there are no columns other than PK because it only serves as a cart. I didn't mention it in the use case, but I don't have user_id (FK) because I want to use the cart function even if I'm not logged in.

users table

Column Type Options
nickname string null: false
email string default: "", null: false
password string null: false
encrypted_password string default: "", null: false
reset_password_token string
reset_password_token string
admin boolean default: false

Association

orders table

Column Type Options
user references foreign_key: true
card references foreign_key: true
product references foreign_key: true
quantity integer null: false
status integer default: 0, null: false
postage integer default: 0, null: false
price integer null: false

Association

cards table

Column Type Options
customer_id string null: false
card_id string null: false
user_id string null: false

Association

addresses table

Column Type Options
destination_family_name string null: false
destination_first_name string null: false
destination_family_name_kana string null: false
destination_family_name_kana string null: false
postcode integer null: false
prefecture_code string null: false
address_city string null: false
address_street string null: false
address_building string
phone_number bigint
user references foreign_key: true, null: false

Association

brands table

Column Type Options
name string

Association has_many :products, dependent: :destroy

sexes table

Column Type Options
name string

Association has_many :products, dependent: :destroy

seasons table

Column Type Options
name string

Association has_many :products, dependent: :destroy

smell_impressions table

Column Type Options
name string

Association has_many :products, dependent: :destroy

smell_types table

Column Type Options
name string

Association has_many :products, dependent: :destroy

user_scenes table

Column Type Options
name string

Association has_many :products, dependent: :destroy

products table

Column Type Options
name string null: false
namelap string null: false
description text
image text
price integer
stock_quantity
brand references foreign_key: true
sex references foreign_key: true
season references foreign_key: true
smell_type references foreign_key: true
main_spice references foreign_key: true
smell_impression references foreign_key: true
use_scene references foreign_key: true

Association

carts table

Column Type Options

Association has_many :line_items, dependent: :destroy

comments table

Column Type Options
user references foreign_key: true
product references foreign_key: true
text text null: false
rate float

Association belongs_to :product belongs_to :user

order_details table

Column Type Options
product references foreign_key: true
order references foreign_key: true
quantity integer null: false

Association

line_items table

Column Type Options
product references foreign_key: true
cart references foreign_key: true
quantity integer default: 0, null: false

Association

Recommended Posts

[Rails] DB design for EC site
[Rails] EC site cart function
[EC2 / Vue / Rails] EC2 deployment procedure for Vue + Rails
Using Material Design for Bootstrap with Rails 5.2
rails db: 〇〇 Summary
Recipe for deploying Rails apps on AWS EC2
Create an EC site with Rails5 ⑤ ~ Customer model ~
Create an EC site with Rails 5 ⑩ ~ Create an order function ~
Launch Rails on EC2
Create an EC site with Rails5 ⑦ ~ Address, Genre model ~
Create an EC site with Rails5 ④ ~ Header and footer ~
rails db: migrate failed!
[Rails] rails db command summary
Deploy RAILS on EC2
Create an EC site with Rails5 ⑥ ~ seed data input ~
For beginners! Automatic deployment with Rails6 + CircleCI + Capistrano + AWS (EC2)
docker-compose command list (for Rails)
Preparation for developing with Rails
Error in rails db: migrate
(For beginners) [Rails] Install Devise
Create an EC site with Rails5 ② ~ Bootstrap4 settings, Controller / action definition ~
[Rails] How to execute "rails db: create" etc. in production environment EC2