Post Views: 266
Mini E-Commerce Store Database Project
Goal: Create a database for a simple online store that handles users, products, and orders.
Tools: MySQL, PostgreSQL, or SQLite (choose one).
🗂️ 1. Design the Tables
📦 products
| Column |
Type |
Description |
| id |
INTEGER |
Primary key |
| name |
TEXT |
Product name |
| price |
DECIMAL |
Product price |
| stock |
INTEGER |
Quantity in stock |
👤 users
| Column |
Type |
Description |
| id |
INTEGER |
Primary key |
| name |
TEXT |
Customer’s name |
| email |
TEXT |
Must be unique |
🧾 orders
| Column |
Type |
Description |
| id |
INTEGER |
Primary key |
| user_id |
INTEGER |
Foreign key (links to users) |
| date |
DATE |
Order date |
📄 order_items
| Column |
Type |
Description |
| id |
INTEGER |
Primary key |
| order_id |
INTEGER |
Foreign key (links to orders) |
| product_id |
INTEGER |
Foreign key (links to products) |
| quantity |
INTEGER |
Quantity ordered |
🛠️ 2. Write the SQL Code
🔨 Create Tables
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT,
email TEXT UNIQUE
);
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT,
price DECIMAL(10, 2),
stock INTEGER
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
user_id INTEGER,
date DATE,
FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE order_items (
id INTEGER PRIMARY KEY,
order_id INTEGER,
product_id INTEGER,
quantity INTEGER,
FOREIGN KEY (order_id) REFERENCES orders(id),
FOREIGN KEY (product_id) REFERENCES products(id)
);
📥 3. Add Sample Data
INSERT INTO users (name, email) VALUES
('Alice', 'alice@mail.com'),
('Bob', 'bob@mail.com');
INSERT INTO products (name, price, stock) VALUES
('Laptop', 500.00, 10),
('Phone', 300.00, 20),
('Headphones', 50.00, 50);
📤 4. Query Ideas (Practice)
✅ Get all products:
SELECT * FROM products;
🛒 Create an order:
INSERT INTO orders (user_id, date) VALUES (1, CURRENT_DATE);
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 2, 1);
🔎 See a user’s orders:
SELECT users.name, products.name AS product, order_items.quantity
FROM order_items
JOIN orders ON order_items.order_id = orders.id
JOIN users ON orders.user_id = users.id
JOIN products ON order_items.product_id = products.id;
🧠 Bonus Challenges
- Add a login system with
username and password fields
- Track
order status (e.g., pending, shipped)
- Add a
categories table for products
Please follow and like us:
