Created
September 8, 2023 10:33
-
-
Save aeruo/76ccb565c7dbadc35c6004b997a2b370 to your computer and use it in GitHub Desktop.
Basic sql database schema for ecommerce sites.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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