現行システムのカオスなテーブル設計を、生成AI（copilot ）を使って正規化してみたい

# はじめに

以下検証をいろいろなモデルでやってみます。

https://qiita.com/Jasmine_teeeeee/items/b17a6337837418aa4836

# 検証環境情報

・DB：PostgreSQL
・生成AIモデル：copilot（claude 3.5 Sonnet）

# やってみよう


## copilotのコードの問題機能を使ってみる

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3778585/29b2d659-99f8-8b38-8224-d81acfed4f8f.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3778585/6c34d31a-bb6d-103d-1726-3d1c4185bce5.png)

この機能は当たり前ですが、コード修正を行う機能なので、
正規化の提案はしてきませんでした。

あれ？create文間違っていたみたいですね。

## 正規化を依頼してみる

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3778585/90eab84a-bc8e-33e6-7b4a-fa5e67762aea.png)

### ER図

```mermaid
erDiagram
    accounts ||--o{ shipping_addresses : has
    accounts ||--o{ organizations : belongs_to
    accounts ||--o{ subordinates : has

    accounts {
        int account_id PK
        varchar username UK
        varchar email UK
        varchar password
        varchar name
        date birth_date
        text address
        varchar phone_number
        varchar role
        varchar status
        timestamp created_at
        timestamp updated_at
    }

    shipping_addresses {
        int address_id PK
        int account_id FK
        varchar postal_code
        varchar prefecture
        varchar city
        varchar building
        int order_number
    }

    organizations {
        int organization_id PK
        int account_id FK
        varchar company
        varchar department
        varchar group
        varchar position
        int order_number
    }

    subordinates {
        int subordinate_id PK
        int account_id FK
        int subordinate_account_id FK
    }
```


### DDL

```
-- アカウントテーブル
CREATE TABLE accounts (
    account_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL,
    birth_date DATE,
    address TEXT,
    phone_number VARCHAR(20),
    role VARCHAR(50) NOT NULL,
    status VARCHAR(20) DEFAULT 'active' NOT NULL,
    created_at TIMESTAMP(6) WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP(6) WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 配送先住所テーブル
CREATE TABLE shipping_addresses (
    address_id SERIAL PRIMARY KEY,
    account_id INTEGER NOT NULL REFERENCES accounts(account_id),
    postal_code VARCHAR(10),
    prefecture VARCHAR(20),
    city VARCHAR(50),
    building VARCHAR(100),
    order_number INTEGER NOT NULL,
    UNIQUE(account_id, order_number)
);

-- 組織情報テーブル
CREATE TABLE organizations (
    organization_id SERIAL PRIMARY KEY,
    account_id INTEGER NOT NULL REFERENCES accounts(account_id),
    company VARCHAR(100),
    department VARCHAR(100),
    group_name VARCHAR(100),
    position VARCHAR(100),
    order_number INTEGER NOT NULL,
    UNIQUE(account_id, order_number)
);

-- 部下関係テーブル
CREATE TABLE subordinates (
    subordinate_id SERIAL PRIMARY KEY,
    account_id INTEGER NOT NULL REFERENCES accounts(account_id),
    subordinate_account_id INTEGER NOT NULL REFERENCES accounts(account_id),
    UNIQUE(account_id, subordinate_account_id)
);

```

### テーブル設計書

```

# テーブル設計書

## accounts テーブル
| カラム名 | データ型 | 制約 | 説明 |
|----------|----------|------|------|
| account_id | SERIAL | PK | アカウントID |
| username | VARCHAR(50) | UK, NOT NULL | ユーザー名 |
| email | VARCHAR(100) | UK, NOT NULL | メールアドレス |
| password | VARCHAR(255) | NOT NULL | パスワード |
| name | VARCHAR(100) | NOT NULL | 氏名 |
| birth_date | DATE | | 生年月日 |
| address | TEXT | | 住所 |
| phone_number | VARCHAR(20) | | 電話番号 |
| role | VARCHAR(50) | NOT NULL | 役割 |
| status | VARCHAR(20) | NOT NULL | ステータス |
| created_at | TIMESTAMP | DEFAULT | 作成日時 |
| updated_at | TIMESTAMP | DEFAULT | 更新日時 |

## shipping_addresses テーブル
| カラム名 | データ型 | 制約 | 説明 |
|----------|----------|------|------|
| address_id | SERIAL | PK | 住所ID |
| account_id | INTEGER | FK, NOT NULL | アカウントID |
| postal_code | VARCHAR(10) | | 郵便番号 |
| prefecture | VARCHAR(20) | | 都道府県 |
| city | VARCHAR(50) | | 市区町村 |
| building | VARCHAR(100) | | 建物名 |
| order_number | INTEGER | NOT NULL | 順序番号 |

## organizations テーブル
| カラム名 | データ型 | 制約 | 説明 |
|----------|----------|------|------|
| organization_id | SERIAL | PK | 組織ID |
| account_id | INTEGER | FK, NOT NULL | アカウントID |
| company | VARCHAR(100) | | 会社名 |
| department | VARCHAR(100) | | 部署名 |
| group_name | VARCHAR(100) | | グループ名 |
| position | VARCHAR(100) | | 役職 |
| order_number | INTEGER | NOT NULL | 順序番号 |

## subordinates テーブル
| カラム名 | データ型 | 制約 | 説明 |
|----------|----------|------|------|
| subordinate_id | SERIAL | PK | 部下関係ID |
| account_id | INTEGER | FK, NOT NULL | 上司のアカウントID |
| subordinate_account_id | INTEGER | FK, NOT NULL | 部下のアカウントID |

```

# まとめ

VSCode上で作業ができるので、こちらの方がやりやすいですね。

（なぜかVSCodeのmarkdownのプレビュー表示ができません。たすけて