This document presents a comprehensive and detailed database schema design, specifically tailored for a robust E-commerce application. Following industry best practices, the schema emphasizes data integrity, scalability, and ease of maintenance. This deliverable includes the Data Definition Language (DDL) SQL scripts for creating the proposed schema, along with detailed explanations and rationale for each design choice.
As part of the "Database Schema Designer" workflow, this step focuses on generating the foundational code for your application's data layer. We have designed a relational database schema that supports core E-commerce functionalities, including user management, product catalog, order processing, and category organization. The generated DDL scripts are production-ready and compatible with PostgreSQL, a leading open-source relational database system.
Our design adheres to the following principles to ensure a high-quality and performant database:
* Primary Keys (PK): Uniquely identify each record in a table.
* Foreign Keys (FK): Enforce referential integrity, ensuring relationships between tables are valid.
* NOT NULL Constraints: Guarantee essential data is always present.
* UNIQUE Constraints: Prevent duplicate values in specified columns.
* CHECK Constraints: (Implicitly handled by data types or explicitly added for specific business rules).
users table, user_id column).created_at and updated_at timestamps for tracking record lifecycle.The E-commerce schema comprises five core tables, representing key entities and their relationships:
users: Stores customer information.categories: Organizes products into logical groups.products: Contains details about available items for sale.orders: Records customer purchases.order_items: Details the specific products included in each order.+-----------+ +------------+ +----------+ +-----------+
| users | | categories | | products | | orders |
|-----------| |------------| |----------| |-----------|
| user_id PK|----< | category_id PK|----<| product_id PK|---<| order_id PK|
| username | | category_name| | product_name| | user_id FK|
| email | | description | | description | | order_date|
| password | +------------+ | price | | total_amount|
| ... | | stock_qty | | status |
| created_at| | category_id FK| | ... |
| updated_at| | created_at | | created_at|
+-----------+ | updated_at | | updated_at|
+----------+ +-----------+
|
| Many-to-Many via
| `order_items`
v
+-------------+
| order_items |
|-------------|
| order_item_id PK|
| order_id FK |-----> orders.order_id
| product_id FK |-----> products.product_id
| quantity |
| price_at_purchase|
+-------------+
This comprehensive study plan is designed to guide you through the process of becoming proficient in database schema design. It covers fundamental concepts, advanced techniques, and practical application, structured over a 12-week period.
Database schema design is the blueprint for how data is structured, stored, and managed within a database. A well-designed schema ensures data integrity, optimizes performance, and facilitates future scalability and maintenance. This plan will equip you with the knowledge and practical skills to design robust and efficient database schemas for various applications.
Goal: To enable you to design, implement, and optimize relational database schemas that are performant, scalable, maintainable, and adhere to industry best practices.
This 12-week schedule provides a structured progression through key topics in database schema design. Each week includes core concepts and practical application.
* Topics: What is a database? Types of databases (RDBMS vs. NoSQL overview). Fundamentals of the Relational Model: Tables, Rows, Columns, Keys (Primary, Foreign, Composite). Basic SQL DDL/DML: CREATE TABLE, INSERT, SELECT.
* Focus: Understanding the foundational building blocks of relational databases.
* Topics: Introduction to ER modeling. Entities, Attributes (simple, composite, multi-valued), Relationships. Cardinality (One-to-One, One-to-Many, Many-to-Many) and Modality (Optional/Mandatory). Drawing ER diagrams using Crow's Foot notation.
* Focus: Translating real-world requirements into a conceptual data model.
* Topics: Weak entities. Generalization/Specialization (Supertype/Subtype relationships). Recursive relationships. Introduction to Conceptual, Logical, and Physical Design phases.
* Focus: Handling complex data relationships and understanding the design lifecycle.
* Topics: Purpose and benefits of normalization. Functional dependencies. First Normal Form (1NF). Second Normal Form (2NF). Third Normal Form (3NF).
* Focus: Eliminating data redundancy and improving data integrity up to 3NF.
* Topics: Boyce-Codd Normal Form (BCNF). Introduction to 4NF and 5NF. Denormalization strategies: when and why to denormalize for performance.
* Focus: Advanced normalization and practical considerations for performance vs. integrity.
* Topics: Translating logical design to physical schema. CREATE TABLE, ALTER TABLE, DROP TABLE statements. Implementing various constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT.
* Focus: Hands-on implementation of schemas using SQL.
* Topics: Understanding indexes (B-tree, Hash). Clustered vs. Non-clustered indexes. When and what to index. Analyzing query execution plans. Basic partitioning concepts.
* Focus: Optimizing query performance and understanding the impact of physical design choices.
* Topics: User accounts, roles, and privileges. Data encryption at rest and in transit (conceptual). Transaction management (ACID properties). Backup and recovery basics (conceptual).
* Focus: Ensuring data security, reliability, and transactional consistency.
* Topics: Introduction to Data Warehousing. OLTP vs. OLAP. Dimensional modeling: Star Schema and Snowflake Schema. Fact tables and Dimension tables.
* Focus: Designing schemas for analytical purposes and business intelligence.
* Topics: Overview of NoSQL types: Key-Value, Document, Column-Family, Graph databases. CAP Theorem. Schema-less vs. Schema-on-read. When to choose NoSQL over RDBMS.
* Focus: Understanding alternative database paradigms and making informed technology choices.
* Topics: Reviewing real-world schema designs from various industries. Common design patterns (e.g., audit trails, versioning, polymorphic associations). Handling temporal data.
* Focus: Applying learned concepts to complex scenarios and learning from established patterns.
* Topics: Apply all learned concepts to design, document, and implement a complete database schema for a moderately complex business problem.
* Focus: Synthesizing knowledge into a practical, demonstrable project.
Upon successful completion of this study plan, you will be able to:
Leverage a mix of theoretical and practical resources to deepen your understanding and skills.
* "Database System Concepts" by Silberschatz, Korth, and Sudarshan: A comprehensive academic text for foundational theory.
* "SQL and Relational Theory: How to Write Accurate SQL Code" by C.J. Date: For a deep dive into the relational model and SQL.
* "Refactoring Databases: Evolutionary Design" by Scott W. Ambler and Pramod J. Sadalage: Practical advice on evolving database designs.
* "The Data Warehouse Toolkit" by Ralph Kimball and Margy Ross: Essential for understanding dimensional modeling.
* "Database Management Essentials" (University of Colorado Boulder on Coursera).
* "Databases" (Stanford University on edX).
* Specific courses on SQL, PostgreSQL/MySQL, Database Design, and Data Modeling.
* Official documentation for popular RDBMS (e.g., PostgreSQL, MySQL, SQL Server, Oracle).
* Official documentation for NoSQL databases (e.g., MongoDB, Cassandra, Neo4j).
* ER Diagramming: Lucidchart, draw.io, dbdiagram.io, MySQL Workbench (for MySQL).
* SQL Clients: DBeaver (multi-database), pgAdmin (PostgreSQL), MySQL Workbench (MySQL), Azure Data Studio (SQL Server).
* Practice Environments: DB-Fiddle, SQL Fiddle, online SQL compilers, or local installations of PostgreSQL/MySQL.
Key checkpoints to track your progress and demonstrate mastery of core concepts.
* Deliverable: Create detailed ER diagrams for a moderately complex business scenario (e.g., a library system or a small social network) that includes entities, attributes, various cardinalities, and at least one supertype/subtype relationship.
* Assessment: Review of ER diagram for correctness, completeness, and adherence to notation standards.
* Deliverable: Take a denormalized dataset or a business problem description and normalize it step-by-step to 3NF/BCNF, clearly identifying functional dependencies at each stage. Justify any denormalization decisions.
* Assessment: Written explanation and diagrammatic representation of normalization steps, evaluated for accuracy and understanding of principles.
* Deliverable: Implement a fully functional database schema using SQL DDL for a small application (e.g., a blog, simple e-commerce product catalog, or project management tool). This should include CREATE TABLE statements with appropriate data types, primary keys, foreign keys, unique constraints, and at least two indexes.
* Assessment: Execution of DDL scripts, verification of schema structure, and demonstration of data integrity through sample data insertion.
* Deliverable: Design and implement a complete database schema for
sql
-- DDL Script for E-commerce Database Schema (PostgreSQL)
-- Generated by PantheraHive's Database Schema Designer
-- =============================================================================
-- Drop Tables (if they exist) in reverse dependency order to avoid foreign key
-- constraint issues during development or re-initialization.
-- !! Use with caution in production environments. !!
-- =============================================================================
DROP TABLE IF EXISTS order_items CASCADE;
DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS products CASCADE;
DROP TABLE IF EXISTS categories CASCADE;
DROP TABLE IF EXISTS users CASCADE;
-- =============================================================================
-- Table: users
-- Description: Stores information about registered users/customers.
-- =============================================================================
CREATE TABLE users (
user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Unique identifier for the user
username VARCHAR(50) UNIQUE NOT NULL, -- Unique username for login
email VARCHAR(255) UNIQUE NOT NULL, -- Unique email address, used for communication and login
password_hash VARCHAR(255) NOT NULL, -- Hashed password for security
first_name VARCHAR(100), -- User's first name
last_name VARCHAR(100), -- User's last name
address TEXT, -- User's primary shipping address
city VARCHAR(100), -- City part of the address
state VARCHAR(100), -- State/Province part of the address
zip_code VARCHAR(20), -- Zip/Postal code part of the address
country VARCHAR(100), -- Country part of the address
phone_number VARCHAR(20), -- User's phone number
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), -- Timestamp when the user record was created
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() -- Timestamp of the last update to the user record
);
COMMENT ON TABLE users IS 'Stores customer and user account information.';
COMMENT ON COLUMN users.user_id IS 'Primary key, unique identifier for each user.';
COMMENT ON COLUMN users.username IS 'Unique username for login purposes.';
COMMENT ON COLUMN users.email IS 'Unique email address, also used for login and notifications.';
COMMENT ON COLUMN users.password_hash IS 'Hashed password for secure authentication.';
COMMENT ON COLUMN users.first_name IS 'User''s given name.';
COMMENT ON COLUMN users.last_name IS 'User''s family name.';
COMMENT ON COLUMN users.address IS 'Primary shipping address.';
COMMENT ON COLUMN users.city IS 'City of the shipping address.';
COMMENT ON COLUMN users.state IS 'State or province of the shipping address.';
COMMENT ON COLUMN users.zip_code IS 'Postal code of the shipping address.';
COMMENT ON COLUMN users.country IS 'Country of the shipping address.';
COMMENT ON COLUMN users.phone_number IS 'User''s contact phone number.';
COMMENT ON COLUMN users.created_at IS 'Timestamp when the user account was created.';
COMMENT ON COLUMN users.updated_at IS 'Timestamp of the last update to the user account details.';
-- =============================================================================
-- Table: categories
-- Description: Defines product categories to organize the product catalog.
-- =============================================================================
CREATE TABLE categories (
category_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Unique identifier for the category
category_name VARCHAR(100) UNIQUE NOT NULL, -- Name of the category (e.g., "Electronics", "Books")
description TEXT -- A brief description of the category
);
COMMENT ON TABLE categories IS 'Organizes products into logical categories.';
COMMENT ON COLUMN categories.category_id IS 'Primary key, unique identifier for each category.';
COMMENT ON COLUMN categories.category_name IS 'Unique name for the product category.';
COMMENT ON COLUMN categories.description IS 'Detailed description of the category.';
-- =============================================================================
-- Table: products
-- Description: Stores details about products available in the E-commerce store.
-- =============================================================================
CREATE TABLE products (
product_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Unique identifier for the product
product_name VARCHAR(255) NOT NULL, -- Name of the product
description TEXT, -- Detailed description of the product
price NUMERIC(10, 2) NOT NULL CHECK (price >= 0), -- Price of the product (e.g., 99.99)
stock_quantity INT NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0), -- Number of units available in stock
category_id UUID NOT NULL, -- Foreign key to the categories table
image_url VARCHAR(255), -- URL to the product's main image
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), -- Timestamp when the product record was created
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), -- Timestamp of the last update to the product record
FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE RESTRICT
);
COMMENT ON TABLE products IS 'Stores details about individual products.';
COMMENT ON COLUMN products.product_id IS 'Primary key, unique identifier for each product.';
COMMENT ON COLUMN products.product_name IS 'Name of the product.';
COMMENT ON COLUMN products.description IS 'Detailed description of the product features and specifications.';
COMMENT ON COLUMN products.price IS 'Selling price of the product.';
COMMENT ON COLUMN products.stock_quantity IS 'Current number of units available in stock.';
COMMENT ON COLUMN products.category_id IS 'Foreign key linking to the product''s category.';
COMMENT ON COLUMN products.image_url IS 'URL for the main product image.';
COMMENT ON COLUMN products.created_at IS 'Timestamp when the product was added to the catalog.';
COMMENT ON COLUMN products.updated_at IS 'Timestamp of the last update to the product details.';
-- =============================================================================
-- Table: orders
-- Description: Records customer orders, including total amount and status.
-- =============================================================================
CREATE TABLE orders (
order_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Unique identifier for the order
user_id UUID NOT NULL, -- Foreign key to the users table (customer who placed the order)
order_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), -- Date and time when the order was placed
total_amount NUMERIC(10, 2) NOT NULL CHECK (total_amount >= 0), -- Total monetary value of the order
status VARCHAR(50) NOT NULL DEFAULT 'pending', -- Current status of the order (e.g., 'pending', 'processing', 'shipped', 'delivered', 'cancelled')
shipping_address TEXT NOT NULL, -- Full shipping address for the order
shipping_city VARCHAR(100) NOT NULL, -- City for shipping
shipping_state VARCHAR(100), -- State/Province for shipping
shipping_zip_code VARCHAR(20) NOT NULL, -- Zip/Postal code for shipping
shipping_country VARCHAR(100) NOT NULL, -- Country for shipping
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), -- Timestamp when the order record was created
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), -- Timestamp of the last update to the order record
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE RESTRICT,
CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled', 'returned'))
);
COMMENT ON TABLE orders IS 'Records customer orders.';
COMMENT ON COLUMN orders.order_id IS 'Primary key, unique identifier for each order.';
COMMENT ON COLUMN orders.user_id IS 'Foreign key linking to the user who placed the order.';
COMMENT ON COLUMN orders.order_date IS 'Date and time the order was placed.';
COMMENT ON COLUMN orders.total_amount IS 'Total amount of the order, including taxes and shipping.';
COMMENT ON COLUMN orders.status IS 'Current status of the order (e.g., pending, shipped).';
COMMENT ON COLUMN orders.shipping_address IS 'Full shipping address for the order.';
COMMENT ON COLUMN orders.shipping_city IS 'City for the shipping address.';
COMMENT ON COLUMN orders.shipping_state IS 'State or province for the shipping
We are pleased to present the comprehensive review and documentation of your proposed database schema, marking the successful completion of the "Database Schema Designer" workflow. This deliverable provides a detailed, actionable overview of the database structure, design rationale, and immediate next steps to facilitate your development and deployment processes.
This document details the finalized database schema design, incorporating best practices for data integrity, scalability, and performance. The schema has been meticulously crafted to support the core functionalities and data requirements identified, ensuring a robust and efficient foundation for your application. This deliverable includes a complete Data Definition Language (DDL) script, a comprehensive data dictionary, an Entity-Relationship Diagram (ERD) overview, and a summary of key design decisions.
The designed database schema is a relational model optimized for transactional integrity and efficient querying. It is structured around several key entities, representing the core components of your application's data. The design emphasizes clear relationships, proper normalization, and appropriate data typing to minimize redundancy and maximize data consistency.
Key Design Principles Applied:
The ERD visually represents the entities within your database and the relationships between them. It illustrates how different tables are linked, including cardinality (one-to-one, one-to-many, many-to-many).
Conceptual ERD Summary:
Users, Products, Orders, Categories, OrderItems) * Users can place many Orders (One-to-Many).
* Products belong to a Category (Many-to-One).
* Orders contain many OrderItems (One-to-Many).
* OrderItems reference Products (Many-to-One).
Note: A visual ERD file (e.g., in PNG, SVG, or Mermaid format) will be provided separately or can be generated using the DDL script with appropriate tools (e.g., dbdiagram.io, draw.io, Lucidchart).
Below is the complete DDL script required to create the database schema. This script is compatible with standard SQL databases (e.g., PostgreSQL, MySQL, SQL Server) with minor adaptations for specific data types or syntax if necessary.
-- Database: YourApplicationDB
-- Schema Version: 1.0.0
-- Drop existing tables if they exist (for development/re-creation purposes)
DROP TABLE IF EXISTS OrderItems;
DROP TABLE IF EXISTS Orders;
DROP TABLE IF EXISTS Products;
DROP TABLE IF EXISTS Categories;
DROP TABLE IF EXISTS Users;
-- 1. Users Table
-- Stores information about application users.
CREATE TABLE Users (
user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Unique identifier for the user
username VARCHAR(50) UNIQUE NOT NULL, -- User's chosen username
email VARCHAR(100) UNIQUE NOT NULL, -- User's email address, used for login/communication
password_hash VARCHAR(255) NOT NULL, -- Hashed password for security
first_name VARCHAR(50), -- User's first name
last_name VARCHAR(50), -- User's last name
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, -- Timestamp of user creation
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP -- Timestamp of last update
);
-- Index for faster username and email lookups
CREATE INDEX idx_users_username ON Users (username);
CREATE INDEX idx_users_email ON Users (email);
-- 2. Categories Table
-- Stores categories for products.
CREATE TABLE Categories (
category_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- Unique identifier for the category
category_name VARCHAR(100) UNIQUE NOT NULL, -- Name of the category (e.g., "Electronics", "Books")
description TEXT -- Optional description of the category
);
-- 3. Products Table
-- Stores information about products available in the application.
CREATE TABLE Products (
product_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Unique identifier for the product
product_name VARCHAR(255) NOT NULL, -- Name of the product
description TEXT, -- Detailed description of the product
price DECIMAL(10, 2) NOT NULL CHECK (price >= 0), -- Price of the product
stock_quantity INT NOT NULL CHECK (stock_quantity >= 0), -- Current stock level
category_id INT, -- Foreign key to Categories table
image_url VARCHAR(255), -- URL to product image
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (category_id) REFERENCES Categories(category_id) ON DELETE SET NULL -- If category is deleted, products become uncategorized
);
-- Index for faster product name and category lookups
CREATE INDEX idx_products_name ON Products (product_name);
CREATE INDEX idx_products_category_id ON Products (category_id);
-- 4. Orders Table
-- Stores information about customer orders.
CREATE TABLE Orders (
order_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Unique identifier for the order
user_id UUID NOT NULL, -- Foreign key to Users table
order_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, -- Date and time the order was placed
total_amount DECIMAL(10, 2) NOT NULL CHECK (total_amount >= 0), -- Total amount of the order
status VARCHAR(50) NOT NULL DEFAULT 'Pending', -- Current status of the order (e.g., 'Pending', 'Processing', 'Shipped', 'Delivered', 'Cancelled')
shipping_address TEXT NOT NULL, -- Shipping address for the order
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE -- If user is deleted, their orders are also deleted
);
-- Index for faster user and order date lookups
CREATE INDEX idx_orders_user_id ON Orders (user_id);
CREATE INDEX idx_orders_order_date ON Orders (order_date);
-- 5. OrderItems Table
-- Stores individual items within an order.
CREATE TABLE OrderItems (
order_item_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), -- Unique identifier for the order item
order_id UUID NOT NULL, -- Foreign key to Orders table
product_id UUID NOT NULL, -- Foreign key to Products table
quantity INT NOT NULL CHECK (quantity > 0), -- Quantity of the product in this order item
unit_price DECIMAL(10, 2) NOT NULL CHECK (unit_price >= 0), -- Price of the product at the time of order
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (order_id) REFERENCES Orders(order_id) ON DELETE CASCADE, -- If order is deleted, its items are also deleted
FOREIGN KEY (product_id) REFERENCES Products(product_id) ON DELETE RESTRICT, -- Prevent deletion of product if it's part of an order item
UNIQUE (order_id, product_id) -- Ensures a product appears only once per order item list
);
-- Index for faster order and product lookups within order items
CREATE INDEX idx_orderitems_order_id ON OrderItems (order_id);
CREATE INDEX idx_orderitems_product_id ON OrderItems (product_id);
-- Optional: Add triggers for 'updated_at' column to automatically update on row modification
-- For PostgreSQL example:
-- CREATE OR REPLACE FUNCTION update_timestamp()
-- RETURNS TRIGGER AS $$
-- BEGIN
-- NEW.updated_at = NOW();
-- RETURN NEW;
-- END;
-- $$ LANGUAGE plpgsql;
-- CREATE TRIGGER set_users_timestamp
-- BEFORE UPDATE ON Users
-- FOR EACH ROW
-- EXECUTE FUNCTION update_timestamp();
-- ... (similar triggers for other tables)
This section provides a detailed breakdown of each table, its columns, and their properties.
Table: Users
* user_id (UUID, PK, NOT NULL): Unique identifier for the user.
* username (VARCHAR(50), UNIQUE, NOT NULL): User's unique login username.
* email (VARCHAR(100), UNIQUE, NOT NULL): User's email address, also unique.
* password_hash (VARCHAR(255), NOT NULL): Securely hashed password.
* first_name (VARCHAR(50)): User's first name.
* last_name (VARCHAR(50)): User's last name.
* created_at (TIMESTAMP WITH TIME ZONE, DEFAULT CURRENT_TIMESTAMP): Timestamp when the user account was created.
* updated_at (TIMESTAMP WITH TIME ZONE, DEFAULT CURRENT_TIMESTAMP): Last time the user's information was updated.
Table: Categories
* category_id (INT, PK, GENERATED ALWAYS AS IDENTITY): Unique identifier for the category.
* category_name (VARCHAR(100), UNIQUE, NOT NULL): Name of the category.
* description (TEXT): Optional detailed description of the category.
Table: Products
* product_id (UUID, PK, NOT NULL): Unique identifier for the product.
* product_name (VARCHAR(255), NOT NULL): Name of the product.
* description (TEXT): Detailed description of the product.
* price (DECIMAL(10, 2), NOT NULL, CHECK >= 0): Current selling price of the product.
* stock_quantity (INT, NOT NULL, CHECK >= 0): Number of units currently in stock.
* category_id (INT, FK Categories): Identifier of the product's category.
* image_url (VARCHAR(255)): URL to the product's image.
* created_at (TIMESTAMP WITH TIME ZONE, DEFAULT CURRENT_TIMESTAMP): Timestamp when the product was added.
* updated_at (TIMESTAMP WITH TIME ZONE, DEFAULT CURRENT_TIMESTAMP): Last time product details were updated.
Table: Orders
* order_id (UUID, PK, NOT NULL): Unique identifier for the order.
* user_id (UUID, FK Users, NOT NULL): Identifier of the user who placed the order.
* order_date (TIMESTAMP WITH TIME ZONE, DEFAULT CURRENT_TIMESTAMP): Date and time the order was placed.
* total_amount (DECIMAL(10, 2), NOT NULL, CHECK >= 0): Total monetary value of the order.
* status (VARCHAR(50), NOT NULL, DEFAULT 'Pending'): Current fulfillment status of the order.
* shipping_address (TEXT, NOT NULL): Full shipping address for the order.
* created_at (TIMESTAMP WITH TIME ZONE, DEFAULT CURRENT_TIMESTAMP): Timestamp when the order was created.
* updated_at (TIMESTAMP WITH TIME ZONE, DEFAULT CURRENT_TIMESTAMP): Last time the order details were updated.
Table: OrderItems
* order_item_id (UUID, PK, NOT NULL): Unique identifier for the order item.
* order_id (UUID, FK Orders, NOT NULL): Identifier of the order this item belongs to.
* product_id (UUID, FK Products, NOT NULL): Identifier of the product included in this order item.
* quantity (INT, NOT NULL, CHECK > 0): Number of units of the product ordered.
* unit_price (DECIMAL(10, 2), NOT NULL, CHECK >= 0): Price of the product at the time it was ordered (historical price).
* created_at (TIMESTAMP WITH TIME ZONE, DEFAULT CURRENT_TIMESTAMP): Timestamp when the order item was created.
* updated_at (TIMESTAMP WITH TIME ZONE, DEFAULT CURRENT_TIMESTAMP): Last time the order item details were updated.
* Constraints: UNIQUE (order_id, product_id) ensures a specific product is listed only once per order.
Users, Products, Orders, OrderItems to provide globally unique identifiers, facilitate distributed systems (if applicable in the future), and avoid sequential ID exposure. Categories uses INT GENERATED ALWAYS AS IDENTITY for simplicity as it's a smaller, more static lookup table.DECIMAL(10, 2) for Monetary Values: Ensures precise storage of currency, avoiding floating-point inaccuracies.TEXT for Descriptions and Addresses: Provides flexibility for longer, multi-line textual content without arbitrary length limits.