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

# はじめに

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

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

# 検証環境情報

・DB：PostgreSQL
・生成AIモデル：V0

# やってみよう

## DDLををインプットにしてみる

### ER図

```mermaid

erDiagram
    ACCOUNTS ||--o{ SHIPPING_ADDRESSES : has
    ACCOUNTS ||--o{ ORGANIZATIONS : belongs_to
    ACCOUNTS ||--o{ SUBORDINATES : manages
    ACCOUNTS }|--|| ACCOUNTS : is_subordinate_of

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

    SHIPPING_ADDRESSES {
        int address_id PK
        int account_id FK
        string postal_code
        string prefecture
        string city
        string building
    }

    ORGANIZATIONS {
        int organization_id PK
        int account_id FK
        string company
        string department
        string group
        string position
    }

    SUBORDINATES {
        int subordinate_id PK
        int account_id FK
        int subordinate_account_id FK
    }

```

## is_subordinate_of　って何ですか？

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3778585/8a7f4d35-b810-1184-85aa-ca41cca92d75.png)

へ～

### DDL

```
-- accounts テーブル
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 WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- shipping_addresses テーブル
CREATE TABLE shipping_addresses (
    address_id SERIAL PRIMARY KEY,
    account_id INTEGER NOT NULL,
    postal_code VARCHAR(10),
    prefecture VARCHAR(20),
    city VARCHAR(50),
    building VARCHAR(100),
    FOREIGN KEY (account_id) REFERENCES accounts(account_id)
);

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

-- subordinates テーブル
CREATE TABLE subordinates (
    subordinate_id SERIAL PRIMARY KEY,
    account_id INTEGER NOT NULL,
    subordinate_account_id INTEGER NOT NULL,
    FOREIGN KEY (account_id) REFERENCES accounts(account_id),
    FOREIGN KEY (subordinate_account_id) REFERENCES accounts(account_id)
);
```

### テーブル設計書

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3778585/0d7c341c-17e7-2542-0a1a-7c6b3a0ff545.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3778585/8f9d417c-88b1-efea-bdbf-19f4f21c7508.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3778585/1edb0f40-be17-ce32-5bb6-e8c9e182c8a2.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3778585/31d7d478-8405-010e-6306-12e61adc72cf.png)

# まとめ

どのモデルもパスワードやロールを別テーブルには持たないんですね。