# ER図（Entity-Relationship Diagram）

## 概要

本ドキュメントは、Bagisto App Demo（オープンソースEコマースモバイルアプリ）のデータベースエンティティ関係図をMermaid形式で記述したものです。

## 全体ER図

```mermaid
erDiagram
    %% 顧客関連
    customers ||--o{ addresses : "has"
    customers }|--|| customer_groups : "belongs to"
    customers ||--o{ orders : "places"
    customers ||--o{ carts : "has"
    customers ||--o{ reviews : "writes"
    customers ||--o{ wishlists : "has"

    %% 商品関連
    products ||--o{ product_flats : "has"
    products ||--o{ product_images : "has"
    products ||--o{ product_inventories : "has"
    products ||--o{ product_attribute_values : "has"
    products }o--|| attribute_families : "belongs to"
    products ||--o{ reviews : "receives"
    products ||--o{ wishlists : "in"
    products ||--o{ downloadable_products : "has"
    products ||--o{ bundle_options : "has"
    products ||--o{ grouped_products : "has"
    products ||--o{ customer_group_prices : "has"

    %% 在庫関連
    product_inventories }o--|| inventory_sources : "from"

    %% 属性関連
    attributes ||--o{ attribute_options : "has"
    product_attribute_values }o--|| attributes : "for"

    %% カート関連
    carts ||--o{ cart_items : "contains"
    carts ||--o{ cart_addresses : "has"
    carts ||--o| cart_shipping_rates : "has"
    carts ||--o| cart_payments : "has"
    cart_items }o--|| products : "for"

    %% 注文関連
    orders ||--o{ order_items : "contains"
    orders ||--o{ order_addresses : "has"
    orders ||--o| order_payments : "has"
    orders ||--o{ shipments : "has"
    orders ||--o{ invoices : "has"
    order_items }o--|| products : "for"

    %% 出荷関連
    shipments ||--o{ shipment_items : "contains"

    %% 請求関連
    invoices ||--o{ invoice_items : "contains"

    %% バンドル商品関連
    bundle_options ||--o{ bundle_option_products : "contains"
    bundle_option_products }o--|| products : "includes"

    %% カテゴリ関連
    categories ||--o{ categories : "parent-child"

    %% チャネル関連
    channels }o--|| currencies : "uses"
    channels ||--o{ locales : "supports"

    %% エンティティ定義
    customers {
        bigint id PK
        varchar email
        varchar first_name
        varchar last_name
        varchar image_url
        date date_of_birth
        varchar phone
        boolean subscribed_to_news_letter
        bigint customer_group_id FK
        boolean is_verified
        boolean status
        timestamp created_at
        timestamp updated_at
    }

    customer_groups {
        bigint id PK
        varchar name
        varchar code
        boolean is_user_defined
        timestamp created_at
        timestamp updated_at
    }

    addresses {
        bigint id PK
        bigint customer_id FK
        varchar first_name
        varchar last_name
        varchar email
        varchar company_name
        text address1
        varchar country
        varchar state
        varchar city
        varchar postcode
        varchar phone
        boolean default_address
        timestamp created_at
        timestamp updated_at
    }

    categories {
        bigint id PK
        varchar name
        text description
        varchar slug
        varchar url_path
        varchar image_url
        varchar banner_url
        varchar logo_url
        int position
        boolean status
        bigint parent_id FK
        timestamp created_at
        timestamp updated_at
    }

    products {
        bigint id PK
        varchar type
        varchar sku
        bigint parent_id FK
        bigint attribute_family_id FK
        timestamp created_at
        timestamp updated_at
    }

    product_flats {
        bigint id PK
        bigint product_id FK
        varchar sku
        varchar name
        text description
        varchar url_key
        boolean new
        boolean featured
        boolean status
        decimal price
        decimal special_price
        varchar locale
        varchar channel
        timestamp created_at
        timestamp updated_at
    }

    product_images {
        bigint id PK
        bigint product_id FK
        varchar path
        varchar url
        int position
        timestamp created_at
        timestamp updated_at
    }

    product_inventories {
        bigint id PK
        bigint product_id FK
        bigint inventory_source_id FK
        int qty
        bigint vendor_id
        timestamp created_at
        timestamp updated_at
    }

    inventory_sources {
        bigint id PK
        varchar code
        varchar name
        text description
        varchar contact_name
        varchar contact_email
        varchar contact_number
        varchar country
        varchar state
        varchar city
        varchar street
        varchar postcode
        int priority
        boolean status
        timestamp created_at
        timestamp updated_at
    }

    attribute_families {
        bigint id PK
        varchar code
        varchar name
        boolean status
        boolean is_user_defined
        timestamp created_at
        timestamp updated_at
    }

    attributes {
        bigint id PK
        varchar code
        varchar admin_name
        varchar type
        int position
        boolean is_filterable
        varchar swatch_type
        timestamp created_at
        timestamp updated_at
    }

    attribute_options {
        bigint id PK
        bigint attribute_id FK
        varchar label
        varchar swatch_value
        int sort_order
        timestamp created_at
        timestamp updated_at
    }

    product_attribute_values {
        bigint id PK
        bigint product_id FK
        bigint attribute_id FK
        varchar locale
        varchar channel
        text text_value
        boolean boolean_value
        int integer_value
        timestamp created_at
        timestamp updated_at
    }

    carts {
        bigint id PK
        bigint customer_id FK
        varchar coupon_code
        int items_count
        int items_qty
        decimal grand_total
        decimal sub_total
        decimal tax_total
        decimal discount_amount
        boolean is_guest
        timestamp created_at
        timestamp updated_at
    }

    cart_items {
        bigint id PK
        bigint cart_id FK
        bigint product_id FK
        varchar type
        varchar name
        int quantity
        decimal price
        decimal total
        json additional
        timestamp created_at
        timestamp updated_at
    }

    cart_addresses {
        bigint id PK
        bigint cart_id FK
        varchar address_type
        text address1
        varchar city
        varchar state
        varchar country
        varchar postcode
        varchar phone
        timestamp created_at
        timestamp updated_at
    }

    cart_shipping_rates {
        bigint id PK
        bigint cart_id FK
        varchar method
        varchar method_title
        decimal price
        timestamp created_at
        timestamp updated_at
    }

    cart_payments {
        bigint id PK
        bigint cart_id FK
        varchar method
        varchar method_title
        timestamp created_at
        timestamp updated_at
    }

    orders {
        bigint id PK
        varchar increment_id
        bigint customer_id FK
        varchar status
        int total_qty_ordered
        decimal grand_total
        decimal sub_total
        decimal tax_amount
        decimal discount_amount
        decimal shipping_amount
        varchar shipping_title
        timestamp created_at
        timestamp updated_at
    }

    order_items {
        bigint id PK
        bigint order_id FK
        bigint product_id FK
        varchar sku
        varchar type
        varchar name
        int qty_ordered
        int qty_shipped
        int qty_invoiced
        int qty_canceled
        int qty_refunded
        decimal price
        decimal total
        json additional
        timestamp created_at
        timestamp updated_at
    }

    order_addresses {
        bigint id PK
        bigint order_id FK
        varchar address_type
        varchar first_name
        varchar last_name
        varchar company_name
        text address1
        varchar city
        varchar state
        varchar country
        varchar postcode
        varchar phone
        timestamp created_at
        timestamp updated_at
    }

    order_payments {
        bigint id PK
        bigint order_id FK
        varchar method
        varchar method_title
        timestamp created_at
        timestamp updated_at
    }

    shipments {
        bigint id PK
        bigint order_id FK
        varchar track_number
        varchar carrier_title
        int total_qty
        timestamp created_at
        timestamp updated_at
    }

    shipment_items {
        bigint id PK
        bigint shipment_id FK
        bigint order_item_id FK
        bigint product_id FK
        varchar name
        varchar sku
        int qty
        timestamp created_at
        timestamp updated_at
    }

    invoices {
        bigint id PK
        bigint order_id FK
        decimal grand_total
        decimal sub_total
        decimal tax_amount
        decimal discount_amount
        decimal shipping_amount
        timestamp created_at
        timestamp updated_at
    }

    invoice_items {
        bigint id PK
        bigint invoice_id FK
        bigint order_item_id FK
        bigint product_id FK
        varchar name
        varchar sku
        int qty
        decimal price
        decimal total
        timestamp created_at
        timestamp updated_at
    }

    reviews {
        bigint id PK
        bigint product_id FK
        bigint customer_id FK
        varchar title
        int rating
        text comment
        varchar status
        varchar customer_name
        timestamp created_at
        timestamp updated_at
    }

    wishlists {
        bigint id PK
        bigint customer_id FK
        bigint product_id FK
        timestamp created_at
        timestamp updated_at
    }

    downloadable_products {
        bigint id PK
        bigint product_id FK
        varchar title
        decimal price
        varchar url
        varchar file
        varchar file_name
        varchar type
        int sort_order
        int downloads
        timestamp created_at
        timestamp updated_at
    }

    bundle_options {
        bigint id PK
        bigint product_id FK
        varchar type
        boolean is_required
        int sort_order
        timestamp created_at
        timestamp updated_at
    }

    bundle_option_products {
        bigint id PK
        bigint bundle_option_id FK
        bigint product_id FK
        int qty
        boolean is_user_defined
        boolean is_default
        int sort_order
        timestamp created_at
        timestamp updated_at
    }

    grouped_products {
        bigint id PK
        bigint product_id FK
        bigint associated_product_id FK
        int qty
        int sort_order
        timestamp created_at
        timestamp updated_at
    }

    customer_group_prices {
        bigint id PK
        bigint product_id FK
        bigint customer_group_id FK
        int qty
        varchar value_type
        decimal value
        timestamp created_at
        timestamp updated_at
    }

    locales {
        bigint id PK
        varchar code
        varchar name
        timestamp created_at
        timestamp updated_at
    }

    currencies {
        bigint id PK
        varchar code
        varchar name
        varchar symbol
        timestamp created_at
        timestamp updated_at
    }

    channels {
        bigint id PK
        varchar code
        varchar name
        bigint root_category_id FK
        bigint base_currency_id FK
        timestamp created_at
        timestamp updated_at
    }
```

## ドメイン別ER図

### 顧客ドメイン

```mermaid
erDiagram
    customers ||--o{ addresses : "has"
    customers }|--|| customer_groups : "belongs to"
    customers ||--o{ wishlists : "has"
    customers ||--o{ reviews : "writes"

    customers {
        bigint id PK
        varchar email
        varchar first_name
        varchar last_name
        varchar image_url
        date date_of_birth
        varchar phone
        boolean subscribed_to_news_letter
        bigint customer_group_id FK
        boolean is_verified
        boolean status
    }

    customer_groups {
        bigint id PK
        varchar name
        varchar code
        boolean is_user_defined
    }

    addresses {
        bigint id PK
        bigint customer_id FK
        varchar first_name
        varchar last_name
        varchar email
        varchar company_name
        text address1
        varchar country
        varchar state
        varchar city
        varchar postcode
        varchar phone
        boolean default_address
    }

    wishlists {
        bigint id PK
        bigint customer_id FK
        bigint product_id FK
    }

    reviews {
        bigint id PK
        bigint product_id FK
        bigint customer_id FK
        varchar title
        int rating
        text comment
        varchar status
    }
```

### 商品ドメイン

```mermaid
erDiagram
    products ||--o{ product_flats : "has"
    products ||--o{ product_images : "has"
    products ||--o{ product_inventories : "has"
    products ||--o{ product_attribute_values : "has"
    products }o--|| attribute_families : "belongs to"
    product_inventories }o--|| inventory_sources : "from"
    product_attribute_values }o--|| attributes : "for"
    attributes ||--o{ attribute_options : "has"

    products {
        bigint id PK
        varchar type
        varchar sku
        bigint parent_id FK
        bigint attribute_family_id FK
    }

    product_flats {
        bigint id PK
        bigint product_id FK
        varchar sku
        varchar name
        text description
        decimal price
        decimal special_price
        varchar locale
        varchar channel
    }

    product_images {
        bigint id PK
        bigint product_id FK
        varchar path
        varchar url
        int position
    }

    product_inventories {
        bigint id PK
        bigint product_id FK
        bigint inventory_source_id FK
        int qty
    }

    inventory_sources {
        bigint id PK
        varchar code
        varchar name
        varchar country
        varchar city
        boolean status
    }

    attribute_families {
        bigint id PK
        varchar code
        varchar name
        boolean status
    }

    attributes {
        bigint id PK
        varchar code
        varchar admin_name
        varchar type
    }

    attribute_options {
        bigint id PK
        bigint attribute_id FK
        varchar label
        varchar swatch_value
    }

    product_attribute_values {
        bigint id PK
        bigint product_id FK
        bigint attribute_id FK
        varchar locale
        text text_value
        int integer_value
    }
```

### 注文ドメイン

```mermaid
erDiagram
    customers ||--o{ orders : "places"
    orders ||--o{ order_items : "contains"
    orders ||--o{ order_addresses : "has"
    orders ||--o| order_payments : "has"
    orders ||--o{ shipments : "has"
    orders ||--o{ invoices : "has"
    order_items }o--|| products : "for"
    shipments ||--o{ shipment_items : "contains"
    invoices ||--o{ invoice_items : "contains"

    customers {
        bigint id PK
        varchar email
        varchar first_name
        varchar last_name
    }

    orders {
        bigint id PK
        varchar increment_id
        bigint customer_id FK
        varchar status
        decimal grand_total
        decimal sub_total
        decimal tax_amount
        decimal shipping_amount
        varchar shipping_title
    }

    order_items {
        bigint id PK
        bigint order_id FK
        bigint product_id FK
        varchar sku
        varchar name
        int qty_ordered
        int qty_shipped
        int qty_invoiced
        decimal price
        decimal total
    }

    order_addresses {
        bigint id PK
        bigint order_id FK
        varchar address_type
        varchar first_name
        varchar last_name
        text address1
        varchar city
        varchar country
        varchar postcode
    }

    order_payments {
        bigint id PK
        bigint order_id FK
        varchar method
        varchar method_title
    }

    shipments {
        bigint id PK
        bigint order_id FK
        varchar track_number
        int total_qty
    }

    shipment_items {
        bigint id PK
        bigint shipment_id FK
        varchar name
        varchar sku
        int qty
    }

    invoices {
        bigint id PK
        bigint order_id FK
        decimal grand_total
        decimal sub_total
        decimal tax_amount
    }

    invoice_items {
        bigint id PK
        bigint invoice_id FK
        varchar name
        varchar sku
        int qty
        decimal price
        decimal total
    }

    products {
        bigint id PK
        varchar type
        varchar sku
    }
```

### カートドメイン

```mermaid
erDiagram
    customers ||--o{ carts : "has"
    carts ||--o{ cart_items : "contains"
    carts ||--o{ cart_addresses : "has"
    carts ||--o| cart_shipping_rates : "has"
    carts ||--o| cart_payments : "has"
    cart_items }o--|| products : "for"

    customers {
        bigint id PK
        varchar email
        varchar first_name
        varchar last_name
    }

    carts {
        bigint id PK
        bigint customer_id FK
        varchar coupon_code
        int items_count
        int items_qty
        decimal grand_total
        decimal sub_total
        decimal tax_total
        decimal discount_amount
        boolean is_guest
    }

    cart_items {
        bigint id PK
        bigint cart_id FK
        bigint product_id FK
        varchar type
        varchar name
        int quantity
        decimal price
        decimal total
        json additional
    }

    cart_addresses {
        bigint id PK
        bigint cart_id FK
        varchar address_type
        text address1
        varchar city
        varchar state
        varchar country
        varchar postcode
        varchar phone
    }

    cart_shipping_rates {
        bigint id PK
        bigint cart_id FK
        varchar method
        varchar method_title
        decimal price
    }

    cart_payments {
        bigint id PK
        bigint cart_id FK
        varchar method
        varchar method_title
    }

    products {
        bigint id PK
        varchar type
        varchar sku
    }
```

## 凡例

| 記号 | 意味 |
| --- | --- |
| `\|\|--o{` | 1対多の関係 |
| `}o--\|\|` | 多対1の関係 |
| `\|\|--o\|` | 1対0または1の関係 |
| `PK` | 主キー（Primary Key） |
| `FK` | 外部キー（Foreign Key） |
