Case Study: Online Clothing Store
Schema Design
Suppose we are designing a database schema for an online clothing store. We want to keep track of our products, orders, and customers.
After some thought, we come up with the following schema:
CREATE TABLE customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
description TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
total_price DECIMAL(10, 2) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
CREATE TABLE order_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
price DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id),
PRIMARY KEY (order_id, product_id)
);
This schema has four tables: Products, Customers, Orders, and Order_items.
The products table stores information about each product, including its name, price, and description. The customers table stores information about each customer, including their name and email address. The orders table stores information about each order, including the customer who placed the order and the total price of the order. Finally, the order_items table stores information about each item in an order, including the order it belongs to, the product it represents, the quantity ordered, and the price at the time of the order.
Inserting Data
Let’s insert some data into our tables.
Products
INSERT INTO products (name, price, description) VALUES
('Men\'s T-Shirt', 19.99, 'This is a comfortable and stylish t-shirt for men.'),
('Women\'s T-Shirt', 18.99, 'This is a comfortable and stylish t-shirt for women.'),
('Men\'s Jeans', 39.99, 'These are comfortable and durable jeans for men.'),
('Women\'s Jeans', 44.99, 'These are comfortable and durable jeans for women.'),
('Men\'s Hoodie', 29.99, 'This is a warm and comfortable hoodie for men.'),
('Women\'s Hoodie', 27.99, 'This is a warm and comfortable hoodie for women.');
Customers
INSERT INTO customers (first_name, last_name, email) VALUES
('John', 'Doe', 'johndoe@example.com'),
('Jane', 'Doe', 'janedoe@example.com'),
('Bob', 'Smith', 'bobsmith@example.com');
Orders
INSERT INTO orders (customer_id, total_price) VALUES
(1, 49.98),
(2, 44.98),
(3, 39.99);
Order_items
INSERT INTO order_items (order_id, product_id, quantity, price) VALUES
(1, 1, 1, 19.99),
(1, 3, 2, 19.98),
(2, 2, 1, 29.99),
(2, 3, 1, 9.99),
(3, 4, 3, 44.97),
(3, 5, 1, 24.99);