【ChatGPT】テーブル設計+DDL

こんにちはあるいはこんばんは

勤怠管理テーブルの設計をChatGPTにお願いしてみました

今回はチェーンプロンプトを用います

0.チェーンプロンプトとは

一つのプロンプトで回答を導くのではなく対話形式で回答を導く方法です

1.ベースとなる設計を指示

あなたはDBAです
Oracleで勤怠管理テーブルを作成します
DDLを作成してください

プロンプト

勤怠管理テーブルのDDLを作成するために、まずテーブルに必要なカラムを決定します。以下は一般的な勤怠管理テーブルに含まれるカラムの例です：

- `EMPLOYEE_ID`: 社員ID
- `ATTENDANCE_DATE`: 勤怠日
- `START_TIME`: 出勤時刻
- `END_TIME`: 退勤時刻
- `WORKING_HOURS`: 勤務時間
- `STATUS`: 勤怠ステータス（例：出勤、遅刻、欠勤など）
- `NOTES`: 備考

これらをもとに、以下のようなDDL（データ定義言語）を作成します。

```sql
CREATE TABLE ATTENDANCE (
EMPLOYEE_ID NUMBER(10) NOT NULL,
ATTENDANCE_DATE DATE NOT NULL,
START_TIME TIMESTAMP,
END_TIME TIMESTAMP,
WORKING_HOURS NUMBER(5, 2),
STATUS VARCHAR2(20),
NOTES VARCHAR2(255),
CONSTRAINT PK_ATTENDANCE PRIMARY KEY (EMPLOYEE_ID, ATTENDANCE_DATE),
CONSTRAINT FK_EMPLOYEE FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID)
);

-- インデックスの作成（必要に応じて）
CREATE INDEX IDX_ATTENDANCE_DATE ON ATTENDANCE (ATTENDANCE_DATE);
CREATE INDEX IDX_ATTENDANCE_STATUS ON ATTENDANCE (STATUS);
```

このDDLスクリプトのポイントは以下の通りです：

1. `EMPLOYEE_ID`と`ATTENDANCE_DATE`を複合主キーとして設定しています。
2. `EMPLOYEE_ID`は他の社員情報テーブル（ここでは`EMPLOYEES`テーブル）への外部キー制約を持っています。
3. 必要に応じてインデックスを追加し、検索のパフォーマンスを向上させます。

このDDLスクリプトを使用して、Oracleデータベースに勤怠管理テーブルを作成することができます。必要に応じてカラムの追加やデータ型の調整を行ってください。

ChatGPT

2.不足項目の追加を指示

制御項目と勤務場所を追加してください

プロンプト

制御項目として、作成日（`CREATED_AT`）や更新日（`UPDATED_AT`）などのカラム、および勤務場所（`WORK_LOCATION`）を追加したDDLを以下に示します。

```sql
CREATE TABLE ATTENDANCE (
EMPLOYEE_ID NUMBER(10) NOT NULL,
ATTENDANCE_DATE DATE NOT NULL,
START_TIME TIMESTAMP,
END_TIME TIMESTAMP,
WORKING_HOURS NUMBER(5, 2),
STATUS VARCHAR2(20),
WORK_LOCATION VARCHAR2(100),
NOTES VARCHAR2(255),
CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
CONSTRAINT PK_ATTENDANCE PRIMARY KEY (EMPLOYEE_ID, ATTENDANCE_DATE),
CONSTRAINT FK_EMPLOYEE FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID)
);

-- インデックスの作成（必要に応じて）
CREATE INDEX IDX_ATTENDANCE_DATE ON ATTENDANCE (ATTENDANCE_DATE);
CREATE INDEX IDX_ATTENDANCE_STATUS ON ATTENDANCE (STATUS);
CREATE INDEX IDX_ATTENDANCE_WORK_LOCATION ON ATTENDANCE (WORK_LOCATION);
```

このDDLスクリプトの変更点は以下の通りです：

1. `WORK_LOCATION`カラムを追加し、勤務場所を記録できるようにしました。
2. `CREATED_AT`カラムを追加し、レコードの作成日時を記録します。
3. `UPDATED_AT`カラムを追加し、レコードの最終更新日時を記録します。デフォルト値として現在のタイムスタンプを設定し、更新時に自動で更新されるようにしています。

これにより、勤怠管理テーブルは勤怠情報だけでなく、制御項目や勤務場所の情報も保持できるようになります。必要に応じてカラムの詳細設定やインデックスの調整を行ってください。

ChatGPT

3.不足項目の追加を指示

アクセスコントロールのために項目を追加してください

プロンプト

アクセスコントロールを行うためには、以下のような項目を追加することが考えられます：

1. `CREATED_BY`: レコードを作成したユーザーID
2. `UPDATED_BY`: レコードを更新したユーザーID

これらの項目を追加したDDLは以下の通りです：

```sql
CREATE TABLE ATTENDANCE (
EMPLOYEE_ID NUMBER(10) NOT NULL,
ATTENDANCE_DATE DATE NOT NULL,
START_TIME TIMESTAMP,
END_TIME TIMESTAMP,
WORKING_HOURS NUMBER(5, 2),
STATUS VARCHAR2(20),
WORK_LOCATION VARCHAR2(100),
NOTES VARCHAR2(255),
CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CREATED_BY NUMBER(10) NOT NULL,
UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UPDATED_BY NUMBER(10),
CONSTRAINT PK_ATTENDANCE PRIMARY KEY (EMPLOYEE_ID, ATTENDANCE_DATE),
CONSTRAINT FK_EMPLOYEE FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID),
CONSTRAINT FK_CREATED_BY FOREIGN KEY (CREATED_BY) REFERENCES USERS(USER_ID),
CONSTRAINT FK_UPDATED_BY FOREIGN KEY (UPDATED_BY) REFERENCES USERS(USER_ID)
);

-- インデックスの作成（必要に応じて）
CREATE INDEX IDX_ATTENDANCE_DATE ON ATTENDANCE (ATTENDANCE_DATE);
CREATE INDEX IDX_ATTENDANCE_STATUS ON ATTENDANCE (STATUS);
CREATE INDEX IDX_ATTENDANCE_WORK_LOCATION ON ATTENDANCE (WORK_LOCATION);
```

このDDLスクリプトの変更点は以下の通りです：

1. `CREATED_BY`カラムを追加し、レコードを作成したユーザーのIDを記録します。
2. `UPDATED_BY`カラムを追加し、レコードを更新したユーザーのIDを記録します。
3. `CREATED_BY`および`UPDATED_BY`カラムは、ユーザー情報を持つ`USERS`テーブルの`USER_ID`を参照する外部キー制約を追加しています。

この設計により、どのユーザーがレコードを作成および更新したかを追跡でき、アクセスコントロールの実装に役立ちます。必要に応じて、カラムの詳細設定やインデックスの調整を行ってください。

ChatGPT

最終的に作成されたDDL

```sql
CREATE TABLE ATTENDANCE (
    EMPLOYEE_ID NUMBER(10) NOT NULL,
    ATTENDANCE_DATE DATE NOT NULL,
    START_TIME TIMESTAMP,
    END_TIME TIMESTAMP,
    WORKING_HOURS NUMBER(5, 2),
    STATUS VARCHAR2(20),
    WORK_LOCATION VARCHAR2(100),
    NOTES VARCHAR2(255),
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CREATED_BY NUMBER(10) NOT NULL,
    UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UPDATED_BY NUMBER(10),
    CONSTRAINT PK_ATTENDANCE PRIMARY KEY (EMPLOYEE_ID, ATTENDANCE_DATE),
    CONSTRAINT FK_EMPLOYEE FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID),
    CONSTRAINT FK_CREATED_BY FOREIGN KEY (CREATED_BY) REFERENCES USERS(USER_ID),
    CONSTRAINT FK_UPDATED_BY FOREIGN KEY (UPDATED_BY) REFERENCES USERS(USER_ID)
);

設計する上で必要な項目を指示していくと、テーブル設計が完成します

もちろん作成されたものは確認が必要です
物理名や桁数、型の見直しなど

ですが先にある程度、命名規則や型について指定して例を示しておけばいいと思います

おまけ

テーブル定義を表形式で作成したり、HTML形式で出力できます

また、論理名を付与するためのCOMMENT ON COLUMNの作成も可能です

ソフトウェア開発にChatGPTは使えるのか？――設計からコーディングまでAIの限界を探る