[RUBY] [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

• client
• Product --Order --Payment
• Delivery address

Master system or transaction system

--Master system

• client
• Product --Transaction system --Order --Payment
• Delivery address

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.

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

Association

• has_many :comments, dependent: :destroy
• has_one :card, dependent: :destroy
• has_one :address, dependent: :destroy
• has_many :orders, dependent: :nullify

orders table

Association

• belongs_to :user
• has_many :products, through: :order_details
• has_many :order_details, dependent: :destroy
• belongs_to :card
• belongs_to :address

cards table

Association

• belongs_to :user
• has_one :order, dependent: :nullify

addresses table

Association

• belongs_to :user
• has_one :order, dependent: :nullify

brands table

Association has_many :products, dependent: :destroy

sexes table

Association has_many :products, dependent: :destroy

seasons table

Association has_many :products, dependent: :destroy

smell_impressions table

Association has_many :products, dependent: :destroy

smell_types table

Association has_many :products, dependent: :destroy

user_scenes table

Association has_many :products, dependent: :destroy

products table

Association

• belongs_to :brand
• belongs_to :sex
• belongs_to :season, optional: true
• belongs_to :smell_type
• belongs_to :main_spice
• belongs_to :smell_impression
• belongs_to :use_scene
• has_many :comments, dependent: :destroy
• has_many :order_details
• has_many :orders, through: :order_details
• has_many :line_items, dependent: :destroy

carts table

Association has_many :line_items, dependent: :destroy

comments table

Association belongs_to :product belongs_to :user

order_details table

Association

• belongs_to :order
• belongs_to :product

line_items table

Association

• belongs_to :product
• belongs_to :cart