# データベース設計書

## 概要

本ドキュメントは、Legacy CMS（コンテンツ管理システム）のデータベース設計を記載したものです。本システムはPHP（Zend Framework）で構築されたCMSであり、記事、ページ、イベント、リソース、ユーザー管理、メール配信などの機能を提供します。データベースはMySQL（MyISAM エンジン）を使用し、UTF-8MB4 文字セットで構成されています。

## テーブル一覧

| テーブル名 | 対応エンティティ | 説明 |
| --- | --- | --- |
| articles | Article | 記事コンテンツを管理 |
| articles_categories | ArticleCategory | 記事のカテゴリを管理 |
| assets | Asset | アップロードファイル（メディア）を管理 |
| assets_folders | AssetFolder | アセットのフォルダ構造を管理 |
| attachments | Attachment | 各種エンティティへの添付ファイルを管理 |
| comments | Comment | 記事・イベント等へのコメントを管理 |
| events | Event | イベント情報を管理 |
| events_categories | EventCategory | イベントのカテゴリを管理 |
| events_venues | EventVenue | イベント開催場所を管理 |
| mail | Mail | 送信メールを管理 |
| mail_groups | MailGroup | メーリングリストグループを管理 |
| mail_subscriptions | MailSubscription | メーリングリストの購読情報を管理 |
| pages | Page | 静的ページコンテンツを管理 |
| resources | Resource | リソース（書籍等）情報を管理 |
| resources_brands | ResourceBrand | リソースのブランド情報を管理 |
| resources_categories | ResourceCategory | リソースのカテゴリを管理 |
| resources_types | ResourceType | リソースの種類を管理 |
| rotators | Rotator | 画像ローテーター（スライダー）を管理 |
| rotators_slides | RotatorSlide | ローテーターのスライドを管理 |
| tags | Tag | 各種エンティティのタグを管理 |
| users | User | ユーザーアカウントを管理 |
| users_privileges | UserPrivilege | ユーザー権限を管理 |
| users_profiles | UserProfile | ユーザープロフィールを管理 |
| users_resources | UserResource | アクセス制御リソース定義を管理 |
| users_roles | UserRole | ユーザーロールを管理 |

## 各テーブル定義

### 1. articles

記事コンテンツを格納するテーブル。ブログ記事やニュース記事などを管理する。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| article_id | int(11) | NO | 主キー、自動採番 |
| article_category | tinyint(4) | NO | カテゴリID（articles_categories.acat_id への外部参照）、デフォルト: 1 |
| article_date | datetime | YES | 記事作成日時 |
| article_title | text | NO | 記事タイトル |
| article_user | int(11) | NO | 作成者ユーザーID（users.user_id への外部参照） |
| article_intro | text | YES | 記事の導入文 |
| article_content | text | YES | 記事本文 |
| article_comments | varchar(1) | NO | コメント許可フラグ（Y/N）、デフォルト: Y |
| article_moderate | varchar(1) | NO | コメント承認制フラグ（Y/N）、デフォルト: N |
| article_sticky | varchar(1) | NO | 固定記事フラグ（Y/N）、デフォルト: N |
| article_edit | datetime | NO | 最終編集日時 |
| article_published | datetime | YES | 公開日時 |
| article_status | varchar(12) | NO | ステータス（draft/published）、デフォルト: draft |

### 2. articles_categories

記事のカテゴリ情報を格納するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| acat_id | int(11) | NO | 主キー、自動採番 |
| acat_title | varchar(60) | NO | カテゴリ名 |

### 3. assets

アップロードされたファイル（画像、ドキュメント等）を管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| asset_id | int(11) | NO | 主キー、自動採番 |
| asset_key | varchar(64) | NO | ファイル識別キー（一意） |
| asset_folder | int(11) | NO | 所属フォルダID（assets_folders.folder_id への外部参照） |
| asset_file | varchar(256) | NO | ファイル名 |
| asset_extension | varchar(12) | NO | ファイル拡張子 |
| asset_name | varchar(256) | NO | 表示名 |
| asset_mime | varchar(64) | NO | MIMEタイプ |
| asset_size | int(11) | NO | ファイルサイズ（バイト） |
| asset_user | int(11) | YES | アップロードユーザーID（users.user_id への外部参照） |
| asset_date | datetime | NO | アップロード日時 |
| asset_modified | datetime | NO | 最終更新日時 |

### 4. assets_folders

アセットを整理するフォルダ構造を管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| folder_id | int(11) | NO | 主キー（UNIQUE）、自動採番 |
| folder_parent | int(11) | NO | 親フォルダID（自己参照）、デフォルト: 0（ルート） |
| folder_name | varchar(256) | NO | フォルダ名 |
| folder_date | datetime | NO | 作成日時 |
| folder_modify | datetime | NO | 最終更新日時 |

### 5. attachments

記事、イベント、メールなどへの添付ファイルを管理する中間テーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| attach_id | int(11) | NO | 主キー、自動採番 |
| attach_type | varchar(1) | NO | 添付先種別（A:記事、E:イベント、M:メール等） |
| attach_slave | int(11) | NO | 添付先エンティティのID |
| attach_asset | varchar(64) | NO | アセットキー（assets.asset_key への外部参照） |

### 6. comments

記事やイベントへのコメントを管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| comment_id | int(11) | NO | 主キー、自動採番 |
| comment_type | varchar(1) | NO | コメント対象種別（A:記事、E:イベント等）、デフォルト: A |
| comment_slave | int(11) | NO | コメント対象エンティティのID、デフォルト: 0 |
| comment_user | mediumint(9) | NO | コメント投稿者ID（users.user_id への外部参照）、デフォルト: 0 |
| comment_date | datetime | NO | 投稿日時 |
| comment_content | text | NO | コメント本文（FULLTEXTインデックス付き） |
| comment_approved | varchar(1) | NO | 承認状態（Y/N）、デフォルト: N |

### 7. events

イベント情報を管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| event_id | int(11) | NO | 主キー、自動採番 |
| event_venue | int(11) | NO | 開催場所ID（events_venues.venue_id への外部参照） |
| event_category | int(11) | NO | カテゴリID（events_categories.ecat_id への外部参照） |
| event_title | varchar(255) | NO | イベントタイトル |
| event_date | datetime | YES | イベント開始日時 |
| event_end | datetime | YES | イベント終了日時 |
| event_description | text | YES | イベント説明 |
| event_url | varchar(255) | YES | 関連URL |
| event_tickets | varchar(255) | YES | チケット情報 |
| event_user | int(11) | NO | 作成者ユーザーID（users.user_id への外部参照） |
| event_comments | varchar(1) | NO | コメント許可フラグ（Y/N）、デフォルト: Y |
| event_moderate | varchar(1) | NO | コメント承認制フラグ（Y/N）、デフォルト: N |
| event_published | datetime | YES | 公開日時 |
| event_status | varchar(16) | NO | ステータス（draft/published）、デフォルト: draft |

### 8. events_categories

イベントカテゴリを管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| ecat_id | int(11) | NO | 主キー、自動採番 |
| ecat_title | varchar(255) | NO | カテゴリ名 |
| ecat_content | text | NO | カテゴリ詳細コンテンツ |
| ecat_description | text | YES | カテゴリ説明 |
| ecat_default | text | NO | デフォルトテンプレート |
| ecat_venue | int(11) | YES | デフォルト会場ID（events_venues.venue_id への外部参照） |
| ecat_status | varchar(12) | NO | ステータス（draft/published）、デフォルト: draft |

### 9. events_venues

イベント開催場所を管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| venue_id | int(11) | NO | 主キー、自動採番 |
| venue_title | varchar(255) | NO | 会場名 |
| venue_address | text | NO | 住所 |
| venue_city | varchar(255) | NO | 市区町村 |
| venue_country | varchar(255) | NO | 国、デフォルト: United Kingdom |
| venue_latitude | float(10,8) | NO | 緯度 |
| venue_longitude | float(10,8) | NO | 経度 |
| venue_description | text | YES | 会場説明 |
| venue_email | varchar(255) | YES | 連絡先メールアドレス |
| venue_url | varchar(255) | YES | 会場ウェブサイト |
| venue_phone | varchar(32) | YES | 電話番号 |
| venue_published | datetime | YES | 公開日時 |
| venue_status | varchar(12) | NO | ステータス（draft/published）、デフォルト: draft |

### 10. mail

送信メールを管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| mail_id | int(11) | NO | 主キー、自動採番 |
| mail_type | varchar(1) | NO | 送信先種別（G:グループ、R:ロール、U:個別ユーザー）、デフォルト: G |
| mail_slave | int(11) | NO | 送信先ID（種別により参照先が異なる） |
| mail_date | datetime | NO | 作成日時 |
| mail_user | int(11) | NO | 作成者ユーザーID（users.user_id への外部参照） |
| mail_subject | text | NO | メール件名 |
| mail_text | text | YES | テキスト形式本文 |
| mail_html | text | YES | HTML形式本文 |
| mail_sent | datetime | YES | 送信日時 |
| mail_status | varchar(12) | NO | ステータス（draft/sent）、デフォルト: draft |

### 11. mail_groups

メーリングリストグループを管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| mgroup_id | int(11) | NO | 主キー、自動採番 |
| mgroup_title | text | NO | グループ名 |
| mgroup_description | text | NO | グループ説明 |
| mgroup_text | text | YES | テキストテンプレート |
| mgroup_html | text | YES | HTMLテンプレート |
| mgroup_open | varchar(1) | NO | 公開購読許可フラグ（Y/N）、デフォルト: Y |
| mgroup_default | varchar(1) | NO | デフォルトグループフラグ（Y/N）、デフォルト: N |
| mgroup_status | varchar(12) | NO | ステータス（draft/published）、デフォルト: draft |

### 12. mail_subscriptions

メーリングリストの購読情報を管理する中間テーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| msub_id | int(11) | NO | 主キー、自動採番 |
| msub_group | int(11) | NO | グループID（mail_groups.mgroup_id への外部参照） |
| msub_user | int(11) | NO | ユーザーID（users.user_id への外部参照） |

### 13. pages

静的ページコンテンツを管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| page_id | int(11) | NO | 主キー、自動採番 |
| page_section | varchar(32) | YES | ページセクション |
| page_slug | varchar(64) | YES | URLスラグ（UNIQUE） |
| page_title | varchar(256) | NO | ページタイトル |
| page_content | text | YES | ページ本文 |
| page_user | int(11) | NO | 作成者ユーザーID（users.user_id への外部参照）、デフォルト: 0 |
| page_date | datetime | NO | 作成日時 |
| page_edit | datetime | NO | 最終編集日時 |
| page_published | datetime | YES | 公開日時 |
| page_status | varchar(16) | NO | ステータス（draft/published）、デフォルト: draft |
| page_protected | varchar(1) | NO | 保護フラグ（Y/N）、デフォルト: N |

### 14. resources

書籍やその他リソース情報を管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| resource_id | int(11) | NO | 主キー、自動採番 |
| resource_type | int(11) | NO | リソース種別ID（resources_types.rtype_id への外部参照）、デフォルト: 1 |
| resource_category | int(11) | NO | カテゴリID（resources_categories.rcat_id への外部参照） |
| resource_date | datetime | NO | 作成日時 |
| resource_title | text | NO | リソースタイトル |
| resource_description | text | NO | 説明 |
| resource_content | text | YES | 詳細コンテンツ |
| resource_asset | varchar(64) | YES | アセットキー（assets.asset_key への外部参照） |
| resource_author | text | YES | 著者 |
| resource_illustrator | text | YES | イラストレーター |
| resource_ISBN | varchar(17) | YES | ISBN番号 |
| resource_brand | int(11) | YES | ブランドID（resources_brands.rbrand_id への外部参照） |
| resource_user | int(11) | NO | 作成者ユーザーID（users.user_id への外部参照）、デフォルト: 1 |
| resource_comments | varchar(1) | NO | コメント許可フラグ（Y/N）、デフォルト: Y |
| resource_moderate | varchar(1) | NO | コメント承認制フラグ（Y/N）、デフォルト: N |
| resource_edit | datetime | YES | 最終編集日時 |
| resource_published | datetime | YES | 公開日時 |
| resource_status | varchar(16) | NO | ステータス（draft/published）、デフォルト: draft |

### 15. resources_brands

リソースのブランド情報を管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| rbrand_id | int(11) | NO | 主キー、自動採番 |
| rbrand_title | text | NO | ブランド名 |
| rbrand_link | varchar(255) | YES | ブランドウェブサイト |
| rbrand_phone | varchar(32) | YES | 電話番号 |

### 16. resources_categories

リソースのカテゴリを管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| rcat_id | int(11) | NO | 主キー、自動採番 |
| rcat_asset | varchar(64) | YES | カテゴリ画像アセットキー（assets.asset_key への外部参照） |
| rcat_title | text | NO | カテゴリ名 |
| rcat_description | text | NO | カテゴリ説明 |
| rcat_content | text | YES | 詳細コンテンツ |
| rcat_status | varchar(12) | NO | ステータス（draft/published）、デフォルト: draft |

### 17. resources_types

リソースの種類を管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| rtype_id | int(11) | NO | 主キー、自動採番 |
| rtype_title | varchar(255) | NO | 種類名（単数形） |
| rtype_plural | varchar(24) | NO | 種類名（複数形） |

### 18. rotators

画像ローテーター（スライダー）の設定を管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| rot_id | int(11) | NO | 主キー（複合）、自動採番 |
| rot_name | varchar(64) | NO | ローテーター名（複合主キー） |
| rot_width | int(11) | NO | 表示幅（ピクセル） |
| rot_height | int(11) | NO | 表示高さ（ピクセル） |
| rot_paging | varchar(1) | NO | ページング表示フラグ（Y/N）、デフォルト: Y |
| rot_delay | int(11) | NO | 切り替え間隔（ミリ秒）、デフォルト: 5000 |

### 19. rotators_slides

ローテーターの各スライドを管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| rots_id | int(11) | NO | 主キー、自動採番 |
| rots_rotator | int(11) | NO | ローテーターID（rotators.rot_id への外部参照） |
| rots_asset | varchar(64) | NO | 画像アセットキー（assets.asset_key への外部参照） |
| rots_title | varchar(256) | NO | スライドタイトル |
| rots_description | text | NO | スライド説明 |
| rots_link | varchar(256) | NO | リンク先URL |
| rots_order | int(11) | NO | 表示順序 |

### 20. tags

各種エンティティに付与されるタグを管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| tag_id | int(11) | NO | 主キー、自動採番 |
| tag_type | varchar(1) | NO | タグ対象種別（A:記事、P:ページ等）、デフォルト: A |
| tag_tag | varchar(60) | NO | タグ文字列 |
| tag_slave | int(11) | NO | タグ対象エンティティのID |

### 21. users

ユーザーアカウント情報を管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| user_id | int(11) | NO | 主キー、自動採番 |
| user_role | int(11) | NO | ロールID（users_roles.role_id への外部参照）、デフォルト: 1 |
| user_alias | varchar(64) | NO | 表示名（エイリアス） |
| user_email | varchar(255) | NO | メールアドレス |
| user_password | varchar(255) | NO | パスワード（ハッシュ化） |
| user_salt | varchar(32) | NO | パスワードソルト |
| user_key | varchar(255) | NO | 認証キー |
| user_date | datetime | NO | 登録日時 |
| user_notifications | varchar(1) | NO | 通知受信フラグ（Y/N）、デフォルト: Y |
| user_mailformat | varchar(4) | NO | メール形式（text/html）、デフォルト: text |
| user_member | varchar(1) | NO | メンバーフラグ（Y/N）、デフォルト: N |
| user_status | varchar(16) | NO | ステータス（active/inactive等）、デフォルト: active |

### 22. users_privileges

ユーザーロールに対する権限を管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| prv_id | int(11) | NO | 主キー、自動採番 |
| prv_resource | varchar(64) | NO | リソース識別子（users_resources.res_resource への外部参照） |
| prv_role | int(11) | NO | ロールID（users_roles.role_id への外部参照） |

### 23. users_profiles

ユーザーの詳細プロフィール情報を管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| upro_id | int(11) | NO | 主キー、自動採番 |
| upro_userid | int(11) | NO | ユーザーID（users.user_id への外部参照） |
| upro_name | text | NO | 氏名（フルネーム） |
| upro_first | text | NO | 名 |
| upro_last | text | NO | 姓 |
| upro_gender | varchar(1) | NO | 性別（M/F/N）、デフォルト: N |
| upro_dob | date | YES | 生年月日 |
| upro_organisation | text | YES | 所属組織 |
| upro_position | text | YES | 役職 |
| upro_address | text | YES | 住所 |
| upro_city | text | YES | 市区町村 |
| upro_postcode | varchar(16) | YES | 郵便番号 |
| upro_country | varchar(2) | NO | 国コード（ISO 3166-1 alpha-2）、デフォルト: GB |
| upro_phone | varchar(32) | YES | 電話番号 |
| upro_blurb | text | YES | 自己紹介 |
| upro_url | varchar(256) | YES | ウェブサイトURL |
| upro_avatar | varchar(256) | YES | アバター画像パス |
| upro_date | datetime | NO | プロフィール作成日時 |

### 24. users_resources

アクセス制御リソースの定義を管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| res_id | int(11) | NO | 主キー、自動採番 |
| res_resource | varchar(64) | NO | リソース識別子（UNIQUE） |
| res_module | varchar(64) | NO | モジュール名 |
| res_group | varchar(64) | NO | リソースグループ名 |
| res_description | text | NO | リソース説明 |

### 25. users_roles

ユーザーロールを管理するテーブル。

| カラム名 | データ型 | NULL | 説明 |
| --- | --- | --- | --- |
| role_id | int(11) | NO | 主キー、自動採番 |
| role_title | varchar(64) | NO | ロール名 |
| role_colour | varchar(7) | NO | 表示色（16進カラーコード） |

## 備考

### データベース構成の特徴

1. **ストレージエンジン**: 全テーブルでMyISAMエンジンを使用。トランザクションや外部キー制約は未サポート。
2. **文字セット**: UTF-8MB4（utf8mb4_general_ci）を使用し、多言語対応および絵文字をサポート。
3. **外部キー制約**: データベースレベルでの外部キー制約は設定されていないが、アプリケーションレベルで参照整合性を維持。

### ポリモーフィック関連

以下のテーブルは種別フィールド（type）とスレーブID（slave）を使用したポリモーフィック関連を実装：

- **attachments**: `attach_type` と `attach_slave` で複数種類のエンティティへの添付をサポート
- **comments**: `comment_type` と `comment_slave` で記事、イベント等へのコメントをサポート
- **tags**: `tag_type` と `tag_slave` で複数種類のエンティティへのタグ付けをサポート
- **mail**: `mail_type` と `mail_slave` でグループ、ロール、個別ユーザーへの送信をサポート

### ステータス管理

多くのコンテンツテーブルで `*_status` カラムを使用したワークフロー管理を実装：
- `draft`: 下書き状態
- `published`: 公開状態

### 初期データ

以下のテーブルには初期データが投入されている：
- **articles_categories**: ID=1 に「Uncategorised」カテゴリ
- **resources_brands**: ID=1 に「Unknown」ブランド
- **rotators**: 複数のローテーター設定（SideBar、HomePage、ResourcesIndex、EventsIndex）
- **users_privileges**: 管理者ロール（ID=3）への各種権限
- **users_resources**: システムリソース定義
- **users_roles**: User、Guest、Administrator の3ロール
