Basic sql database schema for ecommerce sites.

ecommerce-schema.sql

# 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