Skip to content

Instantly share code, notes, and snippets.

@aeruo
Created September 8, 2023 10:33
Show Gist options
  • Save aeruo/76ccb565c7dbadc35c6004b997a2b370 to your computer and use it in GitHub Desktop.
Save aeruo/76ccb565c7dbadc35c6004b997a2b370 to your computer and use it in GitHub Desktop.
Basic sql database schema for ecommerce sites.
# Create a new SQLite database or open an existing one
sqlite3 e_commerce.db
# Define the Users table
CREATE TABLE Users (
user_id INTEGER PRIMARY KEY,
username TEXT,
email TEXT,
password_hash TEXT,
first_name TEXT,
last_name TEXT,
address TEXT,
phone_number TEXT
);
# Define the Categories table
CREATE TABLE Categories (
category_id INTEGER PRIMARY KEY,
category_name TEXT
);
# Define the Products table
CREATE TABLE Products (
product_id INTEGER PRIMARY KEY,
product_name TEXT,
description TEXT,
price REAL,
stock_quantity INTEGER,
category_id INTEGER,
FOREIGN KEY (category_id) REFERENCES Categories (category_id)
);
# Define the ProductImages table
CREATE TABLE ProductImages (
image_id INTEGER PRIMARY KEY,
product_id INTEGER,
image_url TEXT,
FOREIGN KEY (product_id) REFERENCES Products (product_id)
);
# Define the Reviews table
CREATE TABLE Reviews (
review_id INTEGER PRIMARY KEY,
product_id INTEGER,
user_id INTEGER,
rating INTEGER,
comment TEXT,
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (product_id) REFERENCES Products (product_id),
FOREIGN KEY (user_id) REFERENCES Users (user_id)
);
# Define the Orders table
CREATE TABLE Orders (
order_id INTEGER PRIMARY KEY,
user_id INTEGER,
order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
total_price REAL,
status TEXT,
FOREIGN KEY (user_id) REFERENCES Users (user_id)
);
# Define the OrderItems table
CREATE TABLE OrderItems (
order_item_id INTEGER PRIMARY KEY,
order_id INTEGER,
product_id INTEGER,
quantity INTEGER,
item_price REAL,
FOREIGN KEY (order_id) REFERENCES Orders (order_id),
FOREIGN KEY (product_id) REFERENCES Products (product_id)
);
# Define the PaymentMethods table
CREATE TABLE PaymentMethods (
payment_method_id INTEGER PRIMARY KEY,
user_id INTEGER,
method_name TEXT,
account_info TEXT,
FOREIGN KEY (user_id) REFERENCES Users (user_id)
);
# Define the ShippingInfo table
CREATE TABLE ShippingInfo (
shipping_info_id INTEGER PRIMARY KEY,
order_id INTEGER,
recipient_name TEXT,
address TEXT,
city TEXT,
postal_code TEXT,
country TEXT,
phone_number TEXT,
FOREIGN KEY (order_id) REFERENCES Orders (order_id)
);
# Define the Wishlist table
CREATE TABLE Wishlist (
wishlist_id INTEGER PRIMARY KEY,
user_id INTEGER,
FOREIGN KEY (user_id) REFERENCES Users (user_id)
);
# Define the WishlistItems table
CREATE TABLE WishlistItems (
wishlist_item_id INTEGER PRIMARY KEY,
wishlist_id INTEGER,
product_id INTEGER,
FOREIGN KEY (wishlist_id) REFERENCES Wishlist (wishlist_id),
FOREIGN KEY (product_id) REFERENCES Products (product_id)
);
# Exit SQLite
.quit
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment