ChatGPT、GPT-4o、GPT-4o mini 哪個比較適合協助資料表設計?

web - 11月 13, 2024


ChatGPT的 GPT-4o是ChatGPT的最新版本，GPT-4o mini則是GPT-4o較為有效率的版本。

我們來比較一下ChatGPT、GPT-4o、GPT-4o mini 這些版本在資料表設計上，哪個表現比較好?

先準備好固定的問題 : 

(1)需要設計一個線上購物的資料表結構，應該有哪些表單?
(2)給我建立表單 以及插入範例資料的語法
(3)給我planttext UML 來建立 ER diagram
(4)我想知道今年哪個產品銷售最好，應該怎麼寫MySQL指令?
(5)我想知道A級客戶，今年最喜歡哪類產品，應該怎麼寫MySQL指令?

一、首先來看看GPT-4o的回答

以下是針對您的需求提供的設計建議和SQL範例：

(1) 設計線上購物的資料表結構

一般的線上購物系統通常包含以下資料表：

  1. customers：儲存顧客的基本資訊。
  2. products：儲存產品的詳細資訊。
  3. orders：記錄訂單資訊。
  4. order_items：記錄每筆訂單中的產品細節。
  5. categories：分類產品以方便管理和查詢。
  6. payments：儲存每筆訂單的付款資訊。

(2) 建立表單及插入範例資料的SQL語法

建立表單

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    grade VARCHAR(10) NOT NULL,
    email VARCHAR(255)
);

CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    category_name VARCHAR(255) NOT NULL
);

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

CREATE TABLE payments (
    payment_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    amount DECIMAL(10, 2) NOT NULL,
    payment_date DATE,
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

插入範例資料

INSERT INTO customers (name, grade, email) VALUES 
('Alice', 'A', '[email protected]'),
('Bob', 'B', '[email protected]');

INSERT INTO categories (category_name) VALUES 
('Electronics'), ('Furniture');

INSERT INTO products (name, price, category_id) VALUES 
('Laptop', 1200.00, 1), 
('Desk', 300.00, 2);

INSERT INTO orders (customer_id, order_date) VALUES 
(1, '2024-10-30'), 
(2, '2024-10-28');

INSERT INTO order_items (order_id, product_id, quantity) VALUES 
(1, 1, 1), 
(2, 2, 2);

INSERT INTO payments (order_id, amount, payment_date) VALUES 
(1, 1200.00, '2024-10-30'), 
(2, 600.00, '2024-10-28');

(3) PlantUML ER 圖表

@startuml
entity "customers" {
    * customer_id : INT
    --
    name : VARCHAR
    grade : VARCHAR
    email : VARCHAR
}

entity "categories" {
    * category_id : INT
    --
    category_name : VARCHAR
}

entity "products" {
    * product_id : INT
    --
    name : VARCHAR
    price : DECIMAL
    category_id : INT
}

entity "orders" {
    * order_id : INT
    --
    customer_id : INT
    order_date : DATE
}

entity "order_items" {
    * order_item_id : INT
    --
    order_id : INT
    product_id : INT
    quantity : INT
}

entity "payments" {
    * payment_id : INT
    --
    order_id : INT
    amount : DECIMAL
    payment_date : DATE
}

customers ||--o{ orders
orders ||--o{ order_items
order_items }o--|| products
categories ||--o{ products
orders ||--o{ payments
@enduml

(4) 查詢今年銷售額最好的產品

SELECT p.name AS product_name, SUM(oi.quantity * p.price) AS total_sales
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE YEAR(o.order_date) = YEAR(CURDATE())
GROUP BY p.product_id
ORDER BY total_sales DESC
LIMIT 1;

(5) 查詢A級客戶今年最喜歡的產品類型

SELECT c.category_name, COUNT(*) AS purchase_count
FROM orders o
JOIN customers cu ON o.customer_id = cu.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
WHERE cu.grade = 'A' AND YEAR(o.order_date) = YEAR(CURDATE())
GROUP BY c.category_id
ORDER BY purchase_count DESC
LIMIT 1;

二、接著來看看GPT-4o mini的回答

以下是針對你的需求的詳細解答：

(1) 線上購物的資料表結構

在設計線上購物的資料表結構時，建議包括以下表單：

  1. 顧客表 (customers)
  2. 產品表 (products)
  3. 訂單表 (orders)
  4. 訂單項目表 (order_items)
  5. 產品類別表 (categories)

(2) 建立表單及插入範例資料的語法

以下是建立上述表單及插入範例資料的 SQL 語法：

-- 顧客表

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(20),
    grade ENUM('A級', 'B級', 'C級')
);

INSERT INTO customers (name, email, phone, grade) VALUES
('陳小明', '[email protected]', '0912345678', 'A級'),
('李小華', '[email protected]', '0987654321', 'B級');


-- 產品表

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2),
    category_id INT,
    stock INT,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

INSERT INTO products (name, price, category_id, stock) VALUES
('產品A', 100.00, 1, 50),
('產品B', 150.00, 1, 30),
('產品C', 200.00, 2, 20);


-- 訂單表

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

INSERT INTO orders (customer_id, order_date, total) VALUES
(1, '2024-01-10', 300.00),
(2, '2024-02-15', 150.00);


-- 訂單項目表

CREATE TABLE order_items (
    order_item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

INSERT INTO order_items (order_id, product_id, quantity) VALUES
(1, 1, 2),  -- 訂單1中有2個產品A
(1, 2, 1),  -- 訂單1中有1個產品B
(2, 2, 1);  -- 訂單2中有1個產品B


-- 產品類別表

CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

INSERT INTO categories (name) VALUES
('類別1'),
('類別2');


(3) PlantUML UML 來建立 ER Diagram

以下是用 PlantUML 語法來建立 ER diagram 的代碼：

@startuml
entity "顧客" as customers {
    +customer_id : INT
    name : VARCHAR
    email : VARCHAR
    phone : VARCHAR
    grade : ENUM
}

entity "產品" as products {
    +product_id : INT
    name : VARCHAR
    price : DECIMAL
    stock : INT
}

entity "訂單" as orders {
    +order_id : INT
    order_date : DATE
    total : DECIMAL
}

entity "訂單項目" as order_items {
    +order_item_id : INT
    quantity : INT
}

entity "產品類別" as categories {
    +category_id : INT
    name : VARCHAR
}

customers ||--o{ orders : has
orders ||--o{ order_items : contains
order_items }o--|| products : includes
products ||--|| categories : belongs to
@enduml


(4) 查詢今年銷售額最高的產品

要查詢今年銷售額最高的產品，可以使用以下的 MySQL 指令：

SELECT p.name, SUM(oi.quantity * p.price) AS total_sales
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE YEAR(o.order_date) = YEAR(CURDATE())
GROUP BY p.product_id
ORDER BY total_sales DESC
LIMIT 1;


(5) 查詢 A級客戶今年最喜歡哪類產品

以下是查詢 A級客戶今年最喜歡的產品類別的 MySQL 指令：

SELECT c.grade, p.category_id, COUNT(oi.product_id) AS preference_count
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE c.grade = 'A級' AND YEAR(o.order_date) = YEAR(CURDATE())
GROUP BY p.category_id
ORDER BY preference_count DESC
LIMIT 1;


三、最後來看看GPT的回答


(1) 設計線上購物的資料表結構

為了設計一個線上購物的資料表結構，我們可以考慮包含以下幾個表單：

  1. 用戶表（Users） - 儲存用戶的基本資訊。
  2. 產品表（Products） - 儲存產品的詳細資訊。
  3. 訂單表（Orders） - 紀錄每個訂單的總體資訊。
  4. 訂單明細表（Order Items） - 紀錄訂單中的每個產品及其數量。
  5. 分類表（Categories） - 產品分類資訊。
  6. 客戶等級表（Customer Grades） - 紀錄客戶的等級。


(2) 建立表單及插入範例資料的SQL語法

以下是相應的 SQL 建表語句和插入範例資料的語法：

-- 1. 建立用戶表
CREATE TABLE Users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL
);

-- 插入用戶範例資料
INSERT INTO Users (username, email) VALUES ('Alice', '[email protected]'), ('Bob', '[email protected]');

-- 2. 建立產品表
CREATE TABLE Products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    category_id INT
);

-- 插入產品範例資料
INSERT INTO Products (name, price, category_id) VALUES ('Laptop', 800.00, 1), ('Smartphone', 300.00, 1);

-- 3. 建立訂單表
CREATE TABLE Orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2)
);

-- 插入訂單範例資料
INSERT INTO Orders (user_id, order_date, total_amount) VALUES (1, '2023-01-01', 1100.00);

-- 4. 建立訂單明細表
CREATE TABLE Order_Items (
    order_item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT
);

-- 插入訂單明細範例資料
INSERT INTO Order_Items (order_id, product_id, quantity) VALUES (1, 1, 1);

-- 5. 建立分類表
CREATE TABLE Categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(255)
);

-- 插入分類範例資料
INSERT INTO Categories (category_name) VALUES ('Electronics'), ('Clothing');

-- 6. 建立客戶等級表
CREATE TABLE Customer_Grades (
    grade_id INT AUTO_INCREMENT PRIMARY KEY,
    grade_name VARCHAR(50)
);

-- 插入客戶等級範例資料
INSERT INTO Customer_Grades (grade_name) VALUES ('A級客戶'), ('B級客戶');

(3) PlantUML UML 來建立 ER Diagram

以下是 PlantUML 代碼來生成 ER 圖：

@startuml
entity "Users" {
    * user_id : integer
    --
    * username : varchar
    * email : varchar
}

entity "Products" {
    * product_id : integer
    --
    * name : varchar
    * price : decimal
    * category_id : integer
}

entity "Orders" {
    * order_id : integer
    --
    * user_id : integer
    * order_date : date
    * total_amount : decimal
}

entity "Order_Items" {
    * order_item_id : integer
    --
    * order_id : integer
    * product_id : integer
    * quantity : integer
}

entity "Categories" {
    * category_id : integer
    --
    * category_name : varchar
}

entity "Customer_Grades" {
    * grade_id : integer
    --
    * grade_name : varchar
}

Users ||--o{ Orders
Orders ||--o{ Order_Items
Order_Items }--|| Products
Products }--|| Categories
@enduml

針對您的分析需求，以下是相應的 MySQL 指令：

(4) 查詢今年銷售最好的產品：

SELECT p.name, SUM(oi.quantity) AS total_sold
FROM Products p
JOIN Order_Items oi ON p.product_id = oi.product_id
JOIN Orders o ON oi.order_id = o.order_id
WHERE YEAR(o.order_date) = YEAR(CURDATE())
GROUP BY p.product_id
ORDER BY total_sold DESC
LIMIT 1;

(5) 查詢 A級客戶今年最喜歡的產品類型：

SELECT c.category_name, SUM(oi.quantity) AS total_quantity
FROM Categories c
JOIN Products p ON c.category_id = p.category_id
JOIN Order_Items oi ON p.product_id = oi.product_id
JOIN Orders o ON oi.order_id = o.order_id
JOIN Users u ON o.user_id = u.user_id
JOIN Customer_Grades cg ON u.user_id = cg.grade_id
WHERE cg.grade_name = 'A級客戶' AND YEAR(o.order_date) = YEAR(CURDATE())
GROUP BY c.category_id
ORDER BY total_quantity DESC
LIMIT 1;

[評論]

以上三個模型的資料表設計都沒有太大問題，可能因為我們沒有講太多細節的需求，因此細節部分還不太符合我們想要的，我們可以綜合上面的規劃建議，來加速資料表設計。

線上購物的資料表結構，我們修正如下 : 

-- Users 顧客資料表
CREATE TABLE Users (
    u_id INT AUTO_INCREMENT PRIMARY KEY,
    u_name VARCHAR(100) NOT NULL,
    u_email VARCHAR(255) NOT NULL UNIQUE,
    u_account VARCHAR(50) NOT NULL UNIQUE,
    u_password VARCHAR(255) NOT NULL,
    u_rank ENUM('A', 'B', 'C') DEFAULT 'C'
);

因為以上 u_rank 設為ENUM('A', 'B', 'C')，就不能出現'D'，因此如果考慮到擴充性，可以改為 : 
u_rank CHAR(1) DEFAULT '0'，或是 u_rank INT DEFAULT 0，這樣顧客等級就比較具有擴充性。

最後決定修改顧客資料表如下 : 

-- Users 顧客資料表
CREATE TABLE Users (
    u_id INT AUTO_INCREMENT PRIMARY KEY,
    u_name VARCHAR(100) NOT NULL,
    u_email VARCHAR(255) NOT NULL UNIQUE,
    u_account VARCHAR(50) NOT NULL UNIQUE,
    u_password VARCHAR(255) NOT NULL,
    u_rank INT DEFAULT 0
);

u_password之所以設定這麼長，是考慮到現代密碼安全的需求，可以在應用程式端雜湊密碼後再儲存，以防止明文存取，這樣也能儲存長度更長的雜湊值（例如 SHA-256 或 bcrypt）。

-- Categories 產品類別表
CREATE TABLE Categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(255)
);

-- Products 產品資料表
CREATE TABLE products (
    p_id INT AUTO_INCREMENT PRIMARY KEY,
    p_name VARCHAR(100) NOT NULL,
    p_price INT NOT NULL,
    category_id INT NOT NULL,
    p_stock INT DEFAULT 0,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

-- Orders 訂單表頭
CREATE TABLE orders (
    o_id INT PRIMARY KEY AUTO_INCREMENT,
    u_id INT NOT NULL,
    o_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    o_status VARCHAR(1) DEFAULT '0', -- 紀錄訂單狀態, '0' 進行中, '1' 完成, '2' 取消
    FOREIGN KEY (u_id) REFERENCES users(u_id)
);

-- Order_items 訂單表身
CREATE TABLE order_items (
    oi_id INT PRIMARY KEY AUTO_INCREMENT,
    o_id INT,
    p_id INT,
    oi_quantity INT NOT NULL,
    FOREIGN KEY (o_id) REFERENCES orders(o_id),
    FOREIGN KEY (p_id) REFERENCES products(p_id)
);

-- Payments 付款資料表
CREATE TABLE payments (
    payment_id INT PRIMARY KEY AUTO_INCREMENT,
    o_id INT,
    payment_amount DECIMAL(10, 2) NOT NULL,
    payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    p_status VARCHAR(1) DEFAULT '0', -- 紀錄付款狀態, '1' 完成, '2' 取消
    FOREIGN KEY (o_id) REFERENCES orders(o_id)
);

修改資料表單如上，後續再來看看是否還有修改的必要。

標籤 :

張貼留言

0 留言