1. Users Table
This table stores
information about users (customers) who register and log in to the platform.
Table Name: users
|
Column Name |
Data Type |
Description |
|
id |
INTEGER |
Primary Key, Auto
Increment |
|
username |
VARCHAR(255) |
Unique username |
|
email |
VARCHAR(255) |
Unique email address |
|
password |
VARCHAR(255) |
Hashed password |
|
first_name |
VARCHAR(255) |
User's first name |
|
last_name |
VARCHAR(255) |
User's last name |
|
address |
TEXT |
Shipping address |
|
phone_number |
VARCHAR(20) |
User's phone number |
|
role |
VARCHAR(50) |
User's role
(admin/customer) |
|
created_at |
TIMESTAMP |
Account creation
timestamp |
|
updated_at |
TIMESTAMP |
Last account update
timestamp |
2. Products Table
This table stores
information about the products that are available for sale.
Table Name: products
|
Column Name |
Data Type |
Description |
|
id |
INTEGER |
Primary Key, Auto
Increment |
|
name |
VARCHAR(255) |
Name of the product |
|
description |
TEXT |
Detailed product
description |
|
price |
DECIMAL(10,2) |
Price of the product |
|
stock_quantity |
INTEGER |
Quantity of the product
in stock |
|
image |
VARCHAR(255) |
Image file path for the
product |
|
created_at |
TIMESTAMP |
Product creation
timestamp |
|
updated_at |
TIMESTAMP |
Last product update
timestamp |
3. Cart Table
This table stores the
cart details for each user.
Table Name: cart
|
Column Name |
Data Type |
Description |
|
id |
INTEGER |
Primary Key, Auto
Increment |
|
user_id |
INTEGER |
Foreign Key referencing
users(id) |
|
created_at |
TIMESTAMP |
Cart creation timestamp |
|
updated_at |
TIMESTAMP |
Last cart update
timestamp |
4. Cart Items Table
This table stores the
products added to the user's cart.
Table Name: cart_items
|
Column Name |
Data Type |
Description |
|
id |
INTEGER |
Primary Key, Auto
Increment |
|
cart_id |
INTEGER |
Foreign Key referencing
cart(id) |
|
product_id |
INTEGER |
Foreign Key referencing
products(id) |
|
quantity |
INTEGER |
Quantity of the product
in the cart |
5. Orders Table
This table stores
information about orders placed by users.
Table Name: orders
|
Column Name |
Data Type |
Description |
|
id |
INTEGER |
Primary Key, Auto
Increment |
|
user_id |
INTEGER |
Foreign Key referencing
users(id) |
|
total_amount |
DECIMAL(10,2) |
Total cost of the order |
|
shipping_address |
TEXT |
Shipping address for
the order |
|
status |
VARCHAR(50) |
Status of the order
(e.g., "Pending", "Shipped", "Delivered") |
|
created_at |
TIMESTAMP |
Order creation
timestamp |
|
updated_at |
TIMESTAMP |
Last order update
timestamp |
6. Order Items Table
This table stores the
details of the products that are part of a specific order.
Table Name: order_items
|
Column Name |
Data Type |
Description |
|
id |
INTEGER |
Primary Key, Auto
Increment |
|
order_id |
INTEGER |
Foreign Key referencing
orders(id) |
|
product_id |
INTEGER |
Foreign Key referencing
products(id) |
|
quantity |
INTEGER |
Quantity of the product
in the order |
|
price |
DECIMAL(10,2) |
Price of the product at
the time of order |
7. Payments Table
This table stores
information related to payments made by users for their orders.
Table Name: payments
|
Column Name |
Data Type |
Description |
|
id |
INTEGER |
Primary Key, Auto
Increment |
|
order_id |
INTEGER |
Foreign Key referencing
orders(id) |
|
payment_method |
VARCHAR(50) |
Payment method (e.g.,
"Credit Card", "PayPal") |
|
payment_status |
VARCHAR(50) |
Payment status (e.g.,
"Completed", "Failed") |
|
amount |
DECIMAL(10,2) |
Total payment amount |
|
payment_date |
TIMESTAMP |
Date and time of the
payment |
8. Shipping Table
This table stores
information related to the shipping status of orders.
Table Name: shipping
|
Column Name |
Data Type |
Description |
|
id |
INTEGER |
Primary Key, Auto
Increment |
|
order_id |
INTEGER |
Foreign Key referencing
orders(id) |
|
shipping_method |
VARCHAR(50) |
Method of shipping
(e.g., "Standard", "Express") |
|
tracking_number |
VARCHAR(255) |
Tracking number for the
shipment |
|
shipping_status |
VARCHAR(50) |
Shipping status (e.g.,
"Shipped", "In Transit", "Delivered") |
|
shipped_date |
TIMESTAMP |
Date the order was
shipped |
9. Product Reviews Table
This table stores the
reviews left by customers for each product.
Table Name:
product_reviews
|
Column Name |
Data Type |
Description |
|
id |
INTEGER |
Primary Key, Auto
Increment |
|
product_id |
INTEGER |
Foreign Key referencing
products(id) |
|
user_id |
INTEGER |
Foreign Key referencing
users(id) |
|
rating |
INTEGER |
Rating (1-5) given by
the user |
|
review |
TEXT |
Review text from the
user |
|
created_at |
TIMESTAMP |
Review creation
timestamp |
10. Product Categories
Table (Optional)
This table stores product
categories to organize the products into different groups (e.g.,
"Electronics", "Clothing").
Table Name: categories
|
Column Name |
Data Type |
Description |
|
id |
INTEGER |
Primary Key, Auto
Increment |
|
name |
VARCHAR(255) |
Name of the category |
|
description |
TEXT |
Description of the
category |
|
created_at |
TIMESTAMP |
Category creation
timestamp |
|
updated_at |
TIMESTAMP |
Last category update
timestamp |
Conclusion
These are the core tables
used in the Online Shopping Cart project. They allow you to manage
users, products, carts, orders, payments, shipping, and product reviews
effectively. Additionally, you may extend the project by adding more tables or
modifying the existing ones based on specific requirements (e.g., adding a
wishlists table for user wishlists).
SQL queries to create the tables used in the Online Shopping Cart project:
1. Users Table
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) UNIQUE NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
address TEXT,
phone_number VARCHAR(20),
role VARCHAR(50) DEFAULT 'customer',
created_at TIMESTAMP DEFAULT
CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
2. Products Table
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL,
stock_quantity INT DEFAULT 0,
image VARCHAR(255),
created_at TIMESTAMP DEFAULT
CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
3. Cart Table
CREATE TABLE cart (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
created_at TIMESTAMP DEFAULT
CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
);
4. Cart Items Table
CREATE TABLE cart_items (
id INT AUTO_INCREMENT PRIMARY KEY,
cart_id INT,
product_id INT,
quantity INT DEFAULT 1,
FOREIGN KEY (cart_id) REFERENCES cart(id),
FOREIGN KEY (product_id) REFERENCES
products(id)
);
5. Orders Table
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
total_amount DECIMAL(10,2) NOT NULL,
shipping_address TEXT NOT NULL,
status VARCHAR(50) DEFAULT 'Pending',
created_at TIMESTAMP DEFAULT
CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(id)
);
6. Order Items Table
CREATE TABLE order_items (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT DEFAULT 1,
price DECIMAL(10,2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES
orders(id),
FOREIGN KEY (product_id) REFERENCES
products(id)
);
7. Payments Table
CREATE TABLE payments (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT,
payment_method VARCHAR(50) NOT NULL,
payment_status VARCHAR(50) DEFAULT
'Pending',
amount DECIMAL(10,2) NOT NULL,
payment_date TIMESTAMP DEFAULT
CURRENT_TIMESTAMP,
FOREIGN KEY (order_id) REFERENCES
orders(id)
);
8. Shipping Table
CREATE TABLE shipping (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT,
shipping_method VARCHAR(50) NOT NULL,
tracking_number VARCHAR(255),
shipping_status VARCHAR(50) DEFAULT
'Pending',
shipped_date TIMESTAMP,
FOREIGN KEY (order_id) REFERENCES
orders(id)
);
9. Product Reviews Table
CREATE TABLE product_reviews (
id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT,
user_id INT,
rating INT CHECK(rating BETWEEN 1 AND 5),
review TEXT,
created_at TIMESTAMP DEFAULT
CURRENT_TIMESTAMP,
FOREIGN KEY (product_id) REFERENCES
products(id),
FOREIGN KEY (user_id) REFERENCES users(id)
);
10. Product Categories
Table (Optional)
CREATE TABLE categories (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description TEXT,
created_at TIMESTAMP DEFAULT
CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
11. Linking Products and
Categories (Optional)
If you decide to link
products to categories, you will need a relationship table to manage this
many-to-many relationship.
CREATE TABLE product_categories (
product_id INT,
category_id INT,
PRIMARY KEY (product_id, category_id),
FOREIGN KEY (product_id) REFERENCES
products(id),
FOREIGN KEY (category_id) REFERENCES
categories(id)
);