# ER図

## 概要

本ドキュメントは、Legacy CMSのデータベース構造をMermaid形式のER図で表現したものです。テーブル間のリレーションシップを視覚的に把握するための参考資料として使用してください。

## 全体ER図

```mermaid
erDiagram
    %% ユーザー管理
    users ||--o{ users_profiles : "has"
    users ||--o{ users_privileges : "through role"
    users }o--|| users_roles : "belongs to"
    users_roles ||--o{ users_privileges : "has"
    users_resources ||--o{ users_privileges : "referenced by"

    %% 記事管理
    articles }o--|| articles_categories : "belongs to"
    articles }o--|| users : "created by"

    %% ページ管理
    pages }o--|| users : "created by"

    %% イベント管理
    events }o--|| events_categories : "belongs to"
    events }o--|| events_venues : "held at"
    events }o--|| users : "created by"
    events_categories }o--o| events_venues : "default venue"

    %% リソース管理
    resources }o--|| resources_types : "is type of"
    resources }o--|| resources_categories : "belongs to"
    resources }o--o| resources_brands : "brand"
    resources }o--|| users : "created by"
    resources }o--o| assets : "has asset"
    resources_categories }o--o| assets : "has image"

    %% アセット管理
    assets }o--|| assets_folders : "stored in"
    assets }o--o| users : "uploaded by"
    assets_folders }o--o| assets_folders : "parent folder"

    %% ローテーター管理
    rotators_slides }o--|| rotators : "belongs to"
    rotators_slides }o--|| assets : "uses asset"

    %% メール管理
    mail }o--|| users : "created by"
    mail_subscriptions }o--|| mail_groups : "subscribes to"
    mail_subscriptions }o--|| users : "subscriber"

    %% ポリモーフィック関連
    comments }o--|| users : "posted by"
    tags ||--o{ articles : "tags (type A)"
    tags ||--o{ pages : "tags (type P)"
    attachments ||--o{ assets : "references"

    %% テーブル定義
    users {
        int user_id PK
        int user_role FK
        varchar user_alias
        varchar user_email
        varchar user_password
        varchar user_salt
        varchar user_key
        datetime user_date
        varchar user_notifications
        varchar user_mailformat
        varchar user_member
        varchar user_status
    }

    users_roles {
        int role_id PK
        varchar role_title
        varchar role_colour
    }

    users_profiles {
        int upro_id PK
        int upro_userid FK
        text upro_name
        text upro_first
        text upro_last
        varchar upro_gender
        date upro_dob
        text upro_organisation
        text upro_position
        text upro_address
        text upro_city
        varchar upro_postcode
        varchar upro_country
        varchar upro_phone
        text upro_blurb
        varchar upro_url
        varchar upro_avatar
        datetime upro_date
    }

    users_privileges {
        int prv_id PK
        varchar prv_resource FK
        int prv_role FK
    }

    users_resources {
        int res_id PK
        varchar res_resource UK
        varchar res_module
        varchar res_group
        text res_description
    }

    articles {
        int article_id PK
        tinyint article_category FK
        datetime article_date
        text article_title
        int article_user FK
        text article_intro
        text article_content
        varchar article_comments
        varchar article_moderate
        varchar article_sticky
        datetime article_edit
        datetime article_published
        varchar article_status
    }

    articles_categories {
        int acat_id PK
        varchar acat_title
    }

    pages {
        int page_id PK
        varchar page_section
        varchar page_slug UK
        varchar page_title
        text page_content
        int page_user FK
        datetime page_date
        datetime page_edit
        datetime page_published
        varchar page_status
        varchar page_protected
    }

    events {
        int event_id PK
        int event_venue FK
        int event_category FK
        varchar event_title
        datetime event_date
        datetime event_end
        text event_description
        varchar event_url
        varchar event_tickets
        int event_user FK
        varchar event_comments
        varchar event_moderate
        datetime event_published
        varchar event_status
    }

    events_categories {
        int ecat_id PK
        varchar ecat_title
        text ecat_content
        text ecat_description
        text ecat_default
        int ecat_venue FK
        varchar ecat_status
    }

    events_venues {
        int venue_id PK
        varchar venue_title
        text venue_address
        varchar venue_city
        varchar venue_country
        float venue_latitude
        float venue_longitude
        text venue_description
        varchar venue_email
        varchar venue_url
        varchar venue_phone
        datetime venue_published
        varchar venue_status
    }

    resources {
        int resource_id PK
        int resource_type FK
        int resource_category FK
        datetime resource_date
        text resource_title
        text resource_description
        text resource_content
        varchar resource_asset FK
        text resource_author
        text resource_illustrator
        varchar resource_ISBN
        int resource_brand FK
        int resource_user FK
        varchar resource_comments
        varchar resource_moderate
        datetime resource_edit
        datetime resource_published
        varchar resource_status
    }

    resources_types {
        int rtype_id PK
        varchar rtype_title
        varchar rtype_plural
    }

    resources_categories {
        int rcat_id PK
        varchar rcat_asset FK
        text rcat_title
        text rcat_description
        text rcat_content
        varchar rcat_status
    }

    resources_brands {
        int rbrand_id PK
        text rbrand_title
        varchar rbrand_link
        varchar rbrand_phone
    }

    assets {
        int asset_id PK
        varchar asset_key UK
        int asset_folder FK
        varchar asset_file
        varchar asset_extension
        varchar asset_name
        varchar asset_mime
        int asset_size
        int asset_user FK
        datetime asset_date
        datetime asset_modified
    }

    assets_folders {
        int folder_id PK
        int folder_parent FK
        varchar folder_name
        datetime folder_date
        datetime folder_modify
    }

    attachments {
        int attach_id PK
        varchar attach_type
        int attach_slave
        varchar attach_asset FK
    }

    comments {
        int comment_id PK
        varchar comment_type
        int comment_slave
        mediumint comment_user FK
        datetime comment_date
        text comment_content
        varchar comment_approved
    }

    tags {
        int tag_id PK
        varchar tag_type
        varchar tag_tag
        int tag_slave
    }

    rotators {
        int rot_id PK
        varchar rot_name
        int rot_width
        int rot_height
        varchar rot_paging
        int rot_delay
    }

    rotators_slides {
        int rots_id PK
        int rots_rotator FK
        varchar rots_asset FK
        varchar rots_title
        text rots_description
        varchar rots_link
        int rots_order
    }

    mail {
        int mail_id PK
        varchar mail_type
        int mail_slave
        datetime mail_date
        int mail_user FK
        text mail_subject
        text mail_text
        text mail_html
        datetime mail_sent
        varchar mail_status
    }

    mail_groups {
        int mgroup_id PK
        text mgroup_title
        text mgroup_description
        text mgroup_text
        text mgroup_html
        varchar mgroup_open
        varchar mgroup_default
        varchar mgroup_status
    }

    mail_subscriptions {
        int msub_id PK
        int msub_group FK
        int msub_user FK
    }
```

## ドメイン別ER図

### ユーザー管理ドメイン

```mermaid
erDiagram
    users ||--o{ users_profiles : "has profile"
    users }o--|| users_roles : "has role"
    users_roles ||--o{ users_privileges : "granted"
    users_resources ||--o{ users_privileges : "defines access"

    users {
        int user_id PK
        int user_role FK
        varchar user_alias
        varchar user_email
        varchar user_password
        varchar user_salt
        varchar user_key
        datetime user_date
        varchar user_notifications
        varchar user_mailformat
        varchar user_member
        varchar user_status
    }

    users_roles {
        int role_id PK
        varchar role_title
        varchar role_colour
    }

    users_profiles {
        int upro_id PK
        int upro_userid FK
        text upro_name
        text upro_first
        text upro_last
        varchar upro_gender
        date upro_dob
        text upro_organisation
        text upro_position
        text upro_address
        text upro_city
        varchar upro_postcode
        varchar upro_country
        varchar upro_phone
        text upro_blurb
        varchar upro_url
        varchar upro_avatar
        datetime upro_date
    }

    users_privileges {
        int prv_id PK
        varchar prv_resource FK
        int prv_role FK
    }

    users_resources {
        int res_id PK
        varchar res_resource UK
        varchar res_module
        varchar res_group
        text res_description
    }
```

### コンテンツ管理ドメイン（記事・ページ）

```mermaid
erDiagram
    articles }o--|| articles_categories : "categorized"
    articles }o--|| users : "authored by"
    pages }o--|| users : "authored by"

    articles {
        int article_id PK
        tinyint article_category FK
        datetime article_date
        text article_title
        int article_user FK
        text article_intro
        text article_content
        varchar article_comments
        varchar article_moderate
        varchar article_sticky
        datetime article_edit
        datetime article_published
        varchar article_status
    }

    articles_categories {
        int acat_id PK
        varchar acat_title
    }

    pages {
        int page_id PK
        varchar page_section
        varchar page_slug UK
        varchar page_title
        text page_content
        int page_user FK
        datetime page_date
        datetime page_edit
        datetime page_published
        varchar page_status
        varchar page_protected
    }

    users {
        int user_id PK
        varchar user_alias
    }
```

### イベント管理ドメイン

```mermaid
erDiagram
    events }o--|| events_categories : "categorized"
    events }o--|| events_venues : "held at"
    events }o--|| users : "created by"
    events_categories }o--o| events_venues : "default venue"

    events {
        int event_id PK
        int event_venue FK
        int event_category FK
        varchar event_title
        datetime event_date
        datetime event_end
        text event_description
        varchar event_url
        varchar event_tickets
        int event_user FK
        varchar event_comments
        varchar event_moderate
        datetime event_published
        varchar event_status
    }

    events_categories {
        int ecat_id PK
        varchar ecat_title
        text ecat_content
        text ecat_description
        text ecat_default
        int ecat_venue FK
        varchar ecat_status
    }

    events_venues {
        int venue_id PK
        varchar venue_title
        text venue_address
        varchar venue_city
        varchar venue_country
        float venue_latitude
        float venue_longitude
        text venue_description
        varchar venue_email
        varchar venue_url
        varchar venue_phone
        datetime venue_published
        varchar venue_status
    }

    users {
        int user_id PK
        varchar user_alias
    }
```

### リソース管理ドメイン

```mermaid
erDiagram
    resources }o--|| resources_types : "typed as"
    resources }o--|| resources_categories : "categorized"
    resources }o--o| resources_brands : "branded"
    resources }o--|| users : "created by"
    resources }o--o| assets : "has media"
    resources_categories }o--o| assets : "has image"

    resources {
        int resource_id PK
        int resource_type FK
        int resource_category FK
        datetime resource_date
        text resource_title
        text resource_description
        text resource_content
        varchar resource_asset FK
        text resource_author
        text resource_illustrator
        varchar resource_ISBN
        int resource_brand FK
        int resource_user FK
        varchar resource_comments
        varchar resource_moderate
        datetime resource_edit
        datetime resource_published
        varchar resource_status
    }

    resources_types {
        int rtype_id PK
        varchar rtype_title
        varchar rtype_plural
    }

    resources_categories {
        int rcat_id PK
        varchar rcat_asset FK
        text rcat_title
        text rcat_description
        text rcat_content
        varchar rcat_status
    }

    resources_brands {
        int rbrand_id PK
        text rbrand_title
        varchar rbrand_link
        varchar rbrand_phone
    }

    assets {
        int asset_id PK
        varchar asset_key UK
        varchar asset_name
    }

    users {
        int user_id PK
        varchar user_alias
    }
```

### アセット管理ドメイン

```mermaid
erDiagram
    assets }o--|| assets_folders : "stored in"
    assets }o--o| users : "uploaded by"
    assets_folders }o--o| assets_folders : "child of"
    attachments }o--|| assets : "references"

    assets {
        int asset_id PK
        varchar asset_key UK
        int asset_folder FK
        varchar asset_file
        varchar asset_extension
        varchar asset_name
        varchar asset_mime
        int asset_size
        int asset_user FK
        datetime asset_date
        datetime asset_modified
    }

    assets_folders {
        int folder_id PK
        int folder_parent FK
        varchar folder_name
        datetime folder_date
        datetime folder_modify
    }

    attachments {
        int attach_id PK
        varchar attach_type
        int attach_slave
        varchar attach_asset FK
    }

    users {
        int user_id PK
        varchar user_alias
    }
```

### メール配信ドメイン

```mermaid
erDiagram
    mail }o--|| users : "authored by"
    mail_subscriptions }o--|| mail_groups : "subscribes to"
    mail_subscriptions }o--|| users : "subscriber"

    mail {
        int mail_id PK
        varchar mail_type
        int mail_slave
        datetime mail_date
        int mail_user FK
        text mail_subject
        text mail_text
        text mail_html
        datetime mail_sent
        varchar mail_status
    }

    mail_groups {
        int mgroup_id PK
        text mgroup_title
        text mgroup_description
        text mgroup_text
        text mgroup_html
        varchar mgroup_open
        varchar mgroup_default
        varchar mgroup_status
    }

    mail_subscriptions {
        int msub_id PK
        int msub_group FK
        int msub_user FK
    }

    users {
        int user_id PK
        varchar user_alias
        varchar user_email
    }
```

### ローテーター（スライダー）ドメイン

```mermaid
erDiagram
    rotators_slides }o--|| rotators : "belongs to"
    rotators_slides }o--|| assets : "uses image"

    rotators {
        int rot_id PK
        varchar rot_name
        int rot_width
        int rot_height
        varchar rot_paging
        int rot_delay
    }

    rotators_slides {
        int rots_id PK
        int rots_rotator FK
        varchar rots_asset FK
        varchar rots_title
        text rots_description
        varchar rots_link
        int rots_order
    }

    assets {
        int asset_id PK
        varchar asset_key UK
        varchar asset_name
    }
```

## 補足事項

### リレーションシップの表記

| 表記 | 意味 |
|------|------|
| `\|\|--o{` | 1対多（必須 - 任意） |
| `}o--\|\|` | 多対1（任意 - 必須） |
| `}o--o\|` | 多対1（任意 - 任意） |
| `\|\|--\|\|` | 1対1（必須 - 必須） |

### ポリモーフィック関連について

以下のテーブルはポリモーフィック関連を使用しており、ER図上では直接的なリレーションシップとして表現できない部分があります：

1. **comments**: `comment_type` フィールドで対象種別を判別
   - A: articles
   - E: events
   - R: resources

2. **tags**: `tag_type` フィールドで対象種別を判別
   - A: articles
   - P: pages

3. **attachments**: `attach_type` フィールドで対象種別を判別
   - A: articles
   - E: events
   - M: mail

4. **mail**: `mail_type` フィールドで送信先種別を判別
   - G: mail_groups
   - R: users_roles
   - U: users（個別）
