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

はじめに

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

検証環境情報

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

やってみよう

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

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

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

正規化を依頼してみる

ER図

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のプレビュー表示ができません。たすけて