[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
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
| Column | Type | Options |
|---|---|---|
| nickname | string | null: false |
| 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
- has_many :comments, dependent: :destroy
- has_one :card, dependent: :destroy
- has_one :address, dependent: :destroy
- has_many :orders, dependent: :nullify
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
- belongs_to :user
- has_many :products, through: :order_details
- has_many :order_details, dependent: :destroy
- belongs_to :card
- belongs_to :address
cards table
| Column | Type | Options |
|---|---|---|
| customer_id | string | null: false |
| card_id | string | null: false |
| user_id | string | null: false |
Association
- belongs_to :user
- has_one :order, dependent: :nullify
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
- belongs_to :user
- has_one :order, dependent: :nullify
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
- 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
| 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
- belongs_to :order
- belongs_to :product
line_items table
| Column | Type | Options |
|---|---|---|
| product | references | foreign_key: true |
| cart | references | foreign_key: true |
| quantity | integer | default: 0, null: false |
Association
- belongs_to :product
- belongs_to :cart
Recommended Posts