SQLiteで顧客・注文・注文明細を管理するデータベース設計：サンプルコードとERモデル

SQLデータベースにおける顧客、注文、注文明細を管理するテーブル構造

このチュートリアルでは、SQLデータベースにおいて、顧客、注文、注文明細を管理するためのテーブル構造を設計する方法について説明します。このシナリオでは、1人の顧客が複数の注文を行い、1つの注文には複数の注文明細が含まれるという関係性をモデル化します。

関係のモデリング

この関係性を表現するために、3つのテーブルを作成します。

1. 顧客テーブル (customers): 顧客情報を格納します。

各テーブルの構造

顧客テーブル (customers)

注文テーブル (orders)

関係の定義

各テーブル間の関係は以下の制約によって定義されます。

• 顧客テーブルと注文テーブルの関係:
  • 1人の顧客に対して複数の注文が存在 (1対多)
  • 注文テーブルのcustomer_id列は顧客テーブルのcustomer_id列を参照する外部キーである

SQLiteでの実装例

以下のコードは、SQLiteを使用して上記のテーブル構造を作成する例です。

-- 顧客テーブルの作成
CREATE TABLE customers (
  customer_id INT PRIMARY KEY AUTOINCREMENT,
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE,
  phone_number VARCHAR(255)
);

-- 注文テーブルの作成
CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTOINCREMENT,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  order_status VARCHAR(255) NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 注文明細テーブルの作成
CREATE TABLE order_items (
  order_item_id INT PRIMARY KEY AUTOINCREMENT,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(order_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id) -- 商品テーブルとの関連付け
);

補足

• この例では、商品テーブルは省略されていますが、注文明細テーブルと関連付けるために必要です。商品テーブルには、商品ID、商品名、価格などの情報が含まれます。
• 上記のテーブル構造はあくまでも一例であり、実際の要件に合わせて変更する必要があります。
• データベース設計については、正規

-- 顧客テーブルの作成
CREATE TABLE customers (
  customer_id INT PRIMARY KEY AUTOINCREMENT,
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE,
  phone_number VARCHAR(255)
);

-- 注文テーブルの作成
CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTOINCREMENT,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  order_status VARCHAR(255) NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 注文明細テーブルの作成
CREATE TABLE order_items (
  order_item_id INT PRIMARY KEY AUTOINCREMENT,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(order_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id) -- 商品テーブルとの関連付け
);

-- 商品テーブルの作成 (例)
CREATE TABLE products (
  product_id INT PRIMARY KEY AUTOINCREMENT,
  product_name VARCHAR(255) NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL
);

データ操作

顧客の追加

INSERT INTO customers (first_name, last_name, email, phone_number)
VALUES ('田中', '太郎', '[email protected]', '090-1234-5678');

INSERT INTO customers (first_name, last_name, email, phone_number)
VALUES ('佐藤', '花子', '[email protected]', '080-9876-5432');

注文の追加

-- 田中太郎さんの注文を追加
INSERT INTO orders (customer_id, order_date, order_status, total_amount)
VALUES (1, '2024-06-29', '処理中', 12000);

-- 佐藤花子さんの注文を追加
INSERT INTO orders (customer_id, order_date, order_status, total_amount)
VALUES (2, '2024-06-29', '処理中', 8500);

-- 田中太郎さんの注文1件目の注文明細を追加
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (1, 1, 2, 5000); -- 商品ID 1, 数量2個、単価5000円

-- 田中太郎さんの注文1件目の注文明細を追加
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (1, 2, 1, 7000); -- 商品ID 2, 数量1個、単価7000円

-- 佐藤花子さんの注文1件目の注文明細を追加
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (2, 3, 1, 8500); -- 商品ID 3, 数量1個、単価8500円

データの参照

顧客情報の取得

SELECT * FROM customers;

SELECT * FROM orders;

SELECT * FROM order_items;

特定の顧客の注文履歴を取得

SELECT o.*, oi.product_id, oi.quantity, oi.unit_price
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.customer_id = 1; -- 田中太郎さんの注文履歴

このサンプルコードはあくまでも基本的な操作例であり、実際のアプリケーションではより複雑な操作が必要となる場合があります。

• 商品テーブルの内容は省略されていますが、実際の運用には必要です。
• エラー処理やトラン

顧客、注文、注文明細を管理するデータベース構造の設計：代替案

前述のサンプルコードでは、SQLiteを使用して顧客、注文、注文明細を管理するテーブル構造を設計し、データ操作を行う方法を紹介しました。

ここでは、異なるアプローチとして、エンティティ-リレーションシップ (ER) モデルに基づいたテーブル構造を設計する方法を紹介します。

ERモデルは、データベース設計における標準的な手法であり、現実世界のエンティティとその間の関係をモデル化します。

このアプローチでは、以下の3つのエンティティと関係を定義します。

各エンティティの属性

• customer_id (INT, 主キー)
• first_name (VARCHAR(255))
• email (VARCHAR(255), 固有)

注文 (Order)

• customer_id (INT, 外部キー: 顧客テーブルのcustomer_idを参照)
• order_date (DATE)
• total_amount (DECIMAL(10,2))

• quantity (INT)

ERモデルに基づいたテーブル構造

-- 顧客テーブルの作成
CREATE TABLE customers (
  customer_id INT PRIMARY KEY AUTOINCREMENT,
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE,
  phone_number VARCHAR(255)
);

-- 注文テーブルの作成
CREATE TABLE orders (
  order_id INT PRIMARY KEY AUTOINCREMENT,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  order_status VARCHAR(255) NOT NULL,
  total_amount DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 注文明細行テーブルの作成
CREATE TABLE order_items (
  order_item_id INT PRIMARY KEY AUTOINCREMENT,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(order_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id) -- 商品テーブルとの関連付け
);

-- 商品テーブルの作成 (例)
CREATE TABLE products (
  product_id INT PRIMARY KEY AUTOINCREMENT,
  product_name VARCHAR(255) NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL
);

このERモデルに基づいた設計の利点:

• データの整合性を向上させることができます。
• 関連するデータの検索と取得が容易になります。
• 将来的な変更に柔軟に対応できます。

この設計の注意点:

• エラー処理やトランザクション管理などの機能は、アプリケーション側で実装する必要があります。

上記で紹介したERモデルに基づいた設計は、顧客、注文、注文明細を管理するデータベース構造を設計する際のもう一つの選択肢です。

それぞれの設計には一長一短があり、最適な設計は要件や状況によって異なります。

どの設計を選択する場合でも、データベース設計の原則を遵守し、データの整合性と効率性を確保することが重要です。