Project: Database Schema Designer
Workflow Step: 3 of 3 - review_and_document
Date: October 26, 2023
Prepared For: [Customer Name/Organization]
Prepared By: PantheraHive AI Assistant
This document presents the detailed database schema design for the [Specific Application/System Name, e.g., "E-commerce Platform"]. The schema has been meticulously crafted to ensure data integrity, optimal performance, scalability, and maintainability. It adheres to best practices in relational database design, focusing on clarity, efficiency, and extensibility to support current business requirements and future growth. This deliverable includes a comprehensive overview, detailed table and column definitions, relationship mappings, indexing strategies, and considerations for performance, security, and future enhancements.
The primary purpose of this document is to provide a complete and authoritative specification of the database schema. It serves as a foundational reference for development, database administration, and further system design.
The key objectives guiding this schema design were:
The database schema is structured around a core set of entities representing key business concepts. The design emphasizes clear relationships between these entities to facilitate data retrieval and ensure referential integrity.
The schema is logically divided into several functional areas, each represented by a group of interconnected tables. For the purpose of this example, we will focus on a core E-commerce domain, encompassing:
CustomersProductsOrders, OrderItemsWhile a visual ERD cannot be directly rendered in this text-based output, a comprehensive ERD (typically generated using tools like draw.io, Lucidchart, or database design software) showing all tables, columns, primary keys, foreign keys, and relationships with cardinalities (e.g., 1:M, M:M) would be provided as Appendix A: Entity-Relationship Diagram.
Conceptual Overview:
Customers table holds customer information.Products table stores details about available products.Orders table records customer orders.OrderItems table links Orders to Products, detailing specific items within an order.This section provides the Data Definition Language (DDL) scripts required to create the database tables, indexes, and constraints. The DDL is presented in a logical order, ensuring that tables referenced by foreign keys are created first.
-- Disable foreign key checks temporarily if needed for initial load, then re-enable
-- SET foreign_key_checks = 0; -- For MySQL
-- PRAGMA foreign_keys = OFF; -- For SQLite
-- 1. Create Customers Table
CREATE TABLE Customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
phone_number VARCHAR(20),
address_line1 VARCHAR(255),
address_line2 VARCHAR(255),
city VARCHAR(100),
state VARCHAR(100),
postal_code VARCHAR(20),
country VARCHAR(100),
registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_login TIMESTAMP
);
-- 2. Create Products Table
CREATE TABLE Products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
stock_quantity INT NOT NULL CHECK (stock_quantity >= 0),
category VARCHAR(100),
brand VARCHAR(100),
sku VARCHAR(50) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -- For MySQL/MariaDB
-- For PostgreSQL: updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
-- Then use a trigger for auto-update:
-- CREATE FUNCTION update_updated_at_column() RETURNS TRIGGER AS $$
-- BEGIN
-- NEW.updated_at = NOW();
-- RETURN NEW;
-- END;
-- $$ language 'plpgsql';
-- CREATE TRIGGER update_products_updated_at BEFORE UPDATE ON Products FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
);
-- 3. Create Orders Table
CREATE TABLE Orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
total_amount DECIMAL(10, 2) NOT NULL CHECK (total_amount >= 0),
status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending', -- For MySQL/MariaDB
-- For PostgreSQL: status VARCHAR(50) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')),
shipping_address_line1 VARCHAR(255),
shipping_address_line2 VARCHAR(255),
shipping_city VARCHAR(100),
shipping_state VARCHAR(100),
shipping_postal_code VARCHAR(20),
shipping_country VARCHAR(100),
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) ON DELETE RESTRICT ON UPDATE CASCADE
);
-- 4. Create OrderItems Table
CREATE TABLE OrderItems (
order_item_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10, 2) NOT NULL CHECK (unit_price >= 0),
FOREIGN KEY (order_id) REFERENCES Orders(order_id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (product_id) REFERENCES Products(product_id) ON DELETE RESTRICT ON UPDATE CASCADE,
UNIQUE (order_id, product_id) -- Ensures a product appears only once per order item
);
-- 5. Create Indexes for Performance
CREATE INDEX idx_customers_email ON Customers (email);
CREATE INDEX idx_products_category ON Products (category);
CREATE INDEX idx_products_brand ON Products (brand);
CREATE INDEX idx_orders_customer_id ON Orders (customer_id);
CREATE INDEX idx_orders_order_date ON Orders (order_date);
CREATE INDEX idx_orderitems_order_id ON OrderItems (order_id);
CREATE INDEX idx_orderitems_product_id ON OrderItems (product_id);
-- Re-enable foreign key checks if they were disabled
-- SET foreign_key_checks = 1; -- For MySQL
-- PRAGMA foreign_keys = ON; -- For SQLite
This document outlines a detailed, professional study plan designed to equip individuals with the essential knowledge and practical skills required to excel as a Database Schema Designer. This plan is structured to provide a thorough understanding of database principles, design methodologies, and practical implementation, culminating in the ability to design robust, scalable, and efficient database schemas for various applications.
To achieve proficiency in designing, optimizing, and maintaining database schemas, ensuring data integrity, performance, and scalability for diverse application requirements.
This 12-week schedule provides a structured progression through core concepts and practical application. Each week includes theoretical learning, practical exercises, and self-assessment.
* Focus: Introduction to Database Management Systems (DBMS) and Relational Database Management Systems (RDBMS). Understanding data models (conceptual, logical, physical).
* Topics:
* What is a database? Types of databases.
* Introduction to the Relational Model: tables, rows, columns.
* Keys: Primary Key, Foreign Key, Candidate Key, Super Key.
* Integrity Constraints: Entity, Referential, Domain.
* Introduction to SQL: DDL (Data Definition Language) basics (CREATE TABLE, ALTER TABLE, DROP TABLE).
* Basic DML (Data Manipulation Language): INSERT, SELECT, UPDATE, DELETE.
* Activities: Set up a local RDBMS (e.g., PostgreSQL, MySQL). Practice creating simple tables and defining relationships with SQL DDL.
* Focus: Understanding data anomalies and applying normalization techniques to create well-structured, efficient databases.
* Topics:
* Data Anomalies: Insertion, Update, Deletion.
* Normal Forms: 1NF, 2NF, 3NF, BCNF.
* Functional Dependencies and their role in normalization.
* Normalization process: step-by-step application.
* Introduction to 4NF and 5NF (conceptual understanding).
* Denormalization: When and why to denormalize for performance optimization (e.g., data warehousing, reporting).
* Activities: Normalize several provided denormalized datasets to 3NF/BCNF. Analyze scenarios where denormalization might be beneficial.
* Focus: Developing strong conceptual models using ER diagrams to represent real-world entities and their relationships.
* Topics:
* Entities, Attributes (simple, composite, multi-valued, derived).
* Relationships: Degree, Cardinality (1:1, 1:N, N:M), Participation (total, partial).
* Weak Entities, Strong Entities.
* Generalization, Specialization, Aggregation (Supertypes/Subtypes).
* Introduction to Enhanced Entity-Relationship (EER) modeling.
* Tools for ER Diagramming (e.g., Lucidchart, draw.io, MySQL Workbench EER).
* Activities: Create ER diagrams for 3-5 different business scenarios (e.g., library system, online store, hospital management).
* Focus: Translating conceptual ER models into detailed logical schemas and then optimizing for physical implementation.
* Topics:
* Mapping ER/EER models to Relational Schemas: rules and best practices.
* Data Type Selection: Choosing appropriate types for columns (e.g., VARCHAR, INT, DATE, BOOLEAN) and their implications.
* Indexing Strategies: B-tree, Hash, Clustered vs. Non-clustered indexes. When and what to index.
* Views: Creating and using virtual tables.
* Stored Procedures and Functions: Encapsulating logic.
* Triggers: Automating actions on data modification.
* Physical Storage Considerations: Partitioning, Sharding (introduction).
* Security Considerations: Users, Roles, Permissions.
* Activities: Convert a complex ER diagram into a detailed logical schema with chosen data types and constraints. Write SQL DDL for physical implementation, including indexes and views.
* Focus: Deepening understanding of database performance, concurrency, and introduction to alternative database paradigms.
* Topics:
* Database Performance Tuning: Query optimization, execution plans, index analysis.
* Concurrency Control: Transactions, ACID properties, locking mechanisms.
* Backup and Recovery Strategies: Types of backups, recovery models.
* Introduction to NoSQL Databases: Key-Value, Document, Columnar, Graph databases. When to use NoSQL vs. RDBMS.
* Data Warehousing Concepts: OLTP vs. OLAP, Star Schema, Snowflake Schema (brief overview).
* Activities: Analyze slow queries and suggest optimization strategies. Research and compare an RDBMS with a specific NoSQL database for a given use case.
* Focus: Applying all learned concepts to a comprehensive database design project and documentation.
* Topics:
* Full lifecycle database schema design for a medium-complexity application (e.g., social media platform, e-commerce site, content management system).
* Iterative design process: requirements gathering, conceptual design, logical design, physical design, refinement.
* Schema documentation: Data Dictionary, Design Rationale, ERD, SQL DDL.
* Code reviews and peer feedback on designs.
* Activities: Initiate and complete a capstone project. Present the design and rationale.
Upon successful completion of this study plan, you will be able to:
* Articulate the fundamental principles of RDBMS, including the relational model, keys, and integrity constraints.
* Explain the purpose and benefits of database normalization and identify appropriate normal forms for various scenarios.
* Differentiate between conceptual, logical, and physical data models and their respective roles in database design.
* Understand the basic principles of ACID properties and transaction management.
* Recognize the trade-offs between normalized and denormalized designs and justify design decisions based on performance and data integrity requirements.
* Identify scenarios where NoSQL databases might be a more suitable choice than RDBMS.
* Translate complex business requirements into clear and concise Entity-Relationship (ER) or Enhanced Entity-Relationship (EER) diagrams.
* Map ER/EER models accurately into relational schemas, including appropriate table structures, column definitions, and relationship constraints.
* Select optimal data types for columns, considering storage efficiency, data integrity, and performance.
* Design effective indexing strategies to improve query performance.
* Develop views, stored procedures, and triggers to enhance database functionality and security.
* Write robust and efficient SQL DDL statements to create, modify, and manage database schemas.
* Analyze query execution plans and identify bottlenecks for performance optimization.
* Implement basic security measures through user roles and permissions.
* Produce comprehensive database documentation, including data dictionaries, ER diagrams, and design rationales.
* Clearly articulate and defend database design decisions to stakeholders and team members.
A curated list of resources to support your learning journey.
* "Database System Concepts" by Silberschatz, Korth, Sudarshan: A classic, comprehensive textbook covering theoretical foundations.
* "Fundamentals of Database Systems" by Elmasri and Navathe: Another excellent academic resource with strong coverage of data modeling.
* "SQL Antipatterns: Avoiding the Pitfalls of Database Programming" by Bill Karwin: Practical advice on common SQL and schema design mistakes.
* "Designing Data-Intensive Applications" by Martin Kleppmann: For advanced topics, distributed systems, and a deeper understanding of modern data systems.
* Coursera/edX:
* "Database Management Essentials" (University of Colorado)
* "Relational Database Design" (Stanford University via edX)
* "SQL for Data Science" (University of California, Davis)
* Udemy/Pluralsight: Search for courses on "SQL Database Design," "Data Modeling," "Advanced SQL." Look for highly-rated instructors.
* Khan Academy: Offers free, introductory SQL tutorials.
* Official RDBMS Documentation: MySQL, PostgreSQL, SQL Server, Oracle. Essential for understanding specific features, data types, and performance tuning options.
* W3Schools SQL Tutorial: Good for quick syntax lookups and basic understanding.
* PostgreSQL Tutorial (postgresqltutorial.com): Comprehensive and practical for PostgreSQL.
* ER/UML Diagramming:
* Lucidchart / draw.io: Online, collaborative diagramming tools.
* MySQL Workbench (EER Diagrammer): Excellent for MySQL-specific design.
* ER/Studio / PowerDesigner: Professional-grade data modeling tools (often enterprise-level).
* Database Management/SQL Clients:
* DBeaver (Community Edition): Free, universal database client supporting many RDBMS.
* pgAdmin (for PostgreSQL): Official, feature-rich client.
* SQL Developer (for Oracle): Official client.
* DataGrip (JetBrains): Powerful, commercial IDE for databases.
* Local RDBMS:
* PostgreSQL: Open-source, powerful, and widely used.
* MySQL: Popular open-source choice.
* SQLite: File-based, lightweight, excellent for local practice and embedded applications.
Key achievements that mark significant progress throughout the study plan.
* Successfully install and configure a local RDBMS.
* Create a simple database with at least 3 related tables using SQL DDL, defining primary and foreign keys.
* Populate tables with sample data using SQL DML.
* Given a denormalized dataset with functional dependencies, correctly normalize it to 3NF and BCNF.
* Identify and justify a scenario where denormalization might be applied for performance benefits.
* Develop a comprehensive ER/EER diagram for a moderately complex business problem (e.g., a university course registration system), correctly modeling entities, attributes, relationships, and specializations.
This document outlines a comprehensive and detailed database schema for a Project Management System, designed for scalability, data integrity, and efficient querying. This schema is tailored to support core functionalities such as user management, project creation, task assignment, commenting, and file attachments.
The database schema provided below models a typical project management application. It focuses on clarity, normalization, and best practices for relational database design. The design considers common use cases, ensuring that data can be stored and retrieved efficiently while maintaining strong data consistency.
Key Features Supported by this Schema:
At a high level, the system revolves around the following core entities and their relationships:
TaskStatus and TaskPriority for standardized values.Relationships:
User can create and be a member of multiple Projects.Project can have many Users (members).Project contains many Tasks.Task is assigned to one or more Users.Task can have many Comments.Comment is made by one User.Task or Comment can have multiple Files attached.Task has a specific Status and Priority.This section details each table, its purpose, columns, data types, and constraints.
users * user_id (UUID/BIGINT): Primary Key, unique identifier for the user.
* username (VARCHAR(50)): Unique username for login.
* email (VARCHAR(100)): Unique email address, used for communication and potentially login.
* password_hash (VARCHAR(255)): 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): Timestamp when the user account was created (default: current time).
* updated_at (TIMESTAMP WITH TIME ZONE): Timestamp of the last update to the user's profile.
* last_login_at (TIMESTAMP WITH TIME ZONE): Timestamp of the user's last login.
projects * project_id (UUID/BIGINT): Primary Key, unique identifier for the project.
* project_name (VARCHAR(100)): Name of the project.
* description (TEXT): Detailed description of the project.
* owner_id (UUID/BIGINT): Foreign Key to users.user_id, the user who owns/created the project.
* start_date (DATE): Planned start date of the project.
* end_date (DATE): Planned end date of the project.
* status (VARCHAR(20)): Current status of the project (e.g., 'Planning', 'Active', 'Completed', 'On Hold').
* created_at (TIMESTAMP WITH TIME ZONE): Timestamp when the project was created.
* updated_at (TIMESTAMP WITH TIME ZONE): Timestamp of the last update to the project.
project_membersusers and projects, defining who is part of which project and their role. * project_member_id (UUID/BIGINT): Primary Key, unique identifier for the membership.
* project_id (UUID/BIGINT): Foreign Key to projects.project_id.
* user_id (UUID/BIGINT): Foreign Key to users.user_id.
* role (VARCHAR(50)): Role of the user within the project (e.g., 'Admin', 'Member', 'Viewer').
* joined_at (TIMESTAMP WITH TIME ZONE): Timestamp when the user joined the project.
* left_at (TIMESTAMP WITH TIME ZONE): Timestamp if the user left the project (nullable).
(project_id, user_id) to prevent duplicate memberships.task_status * status_id (SMALLINT): Primary Key, unique identifier for the status.
* status_name (VARCHAR(50)): Name of the status (e.g., 'To Do', 'In Progress', 'Done', 'Blocked').
* description (TEXT): Optional description of the status.
status_name.task_priority * priority_id (SMALLINT): Primary Key, unique identifier for the priority.
* priority_name (VARCHAR(50)): Name of the priority (e.g., 'Low', 'Medium', 'High', 'Urgent').
* level (SMALLINT): Numeric level for ordering priorities (e.g., 1 for Low, 4 for Urgent).
priority_name, Unique on level.tasks * task_id (UUID/BIGINT): Primary Key, unique identifier for the task.
* project_id (UUID/BIGINT): Foreign Key to projects.project_id, the project this task belongs to.
* title (VARCHAR(255)): Title of the task.
* description (TEXT): Detailed description of the task.
* creator_id (UUID/BIGINT): Foreign Key to users.user_id, the user who created the task.
* status_id (SMALLINT): Foreign Key to task_status.status_id, current status of the task.
* priority_id (SMALLINT): Foreign Key to task_priority.priority_id, priority of the task.
* due_date (DATE): Due date for the task.
* completed_at (TIMESTAMP WITH TIME ZONE): Timestamp when the task was marked as completed (nullable).
* created_at (TIMESTAMP WITH TIME ZONE): Timestamp when the task was created.
* updated_at (TIMESTAMP WITH TIME ZONE): Timestamp of the last update to the task.
task_assignmentstasks and users). * assignment_id (UUID/BIGINT): Primary Key, unique identifier for the assignment.
* task_id (UUID/BIGINT): Foreign Key to tasks.task_id.
* user_id (UUID/BIGINT): Foreign Key to users.user_id, the user assigned to the task.
* assigned_at (TIMESTAMP WITH TIME ZONE): Timestamp when the user was assigned.
(task_id, user_id) to prevent duplicate assignments.comments * comment_id (UUID/BIGINT): Primary Key, unique identifier for the comment.
* task_id (UUID/BIGINT): Foreign Key to tasks.task_id, the task this comment belongs to.
* user_id (UUID/BIGINT): Foreign Key to users.user_id, the user who made the comment.
* parent_comment_id (UUID/BIGINT): Self-referencing Foreign Key to comments.comment_id for nested comments (nullable).
* content (TEXT): The actual text content of the comment.
* created_at (TIMESTAMP WITH TIME ZONE): Timestamp when the comment was created.
* updated_at (TIMESTAMP WITH TIME ZONE): Timestamp of the last update to the comment.
files * file_id (UUID/BIGINT): Primary Key, unique identifier for the file.
* uploader_id (UUID/BIGINT): Foreign Key to users.user_id, the user who uploaded the file.
* task_id (UUID/BIGINT): Foreign Key to tasks.task_id (nullable, if attached to a comment).
* comment_id (UUID/BIGINT): Foreign Key to comments.comment_id (nullable, if attached to a task).
* file_name (VARCHAR(255)): Original name of the uploaded file.
* file_type (VARCHAR(50)): MIME type of the file (e.g., 'image/jpeg', 'application/pdf').
* file_size (BIGINT): Size of the file in bytes.
* storage_path (VARCHAR(255)): Path or URL where the file is stored (e.g., S3 URL, local file path).
* uploaded_at (TIMESTAMP WITH TIME ZONE): Timestamp when the file was uploaded.
task_id or comment_id (but not both) is present.This section provides the production-ready SQL Data Definition Language (DDL) script to create the schema in a PostgreSQL database. UUIDs are used for primary keys to ensure global uniqueness and prevent sequential ID exposure.
-- Ensure UUID extension is available for UUID primary keys
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- -----------------------------------------------------
-- Table `users`
-- Stores information about registered users.
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
user_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
last_login_at TIMESTAMP WITH TIME ZONE,
CONSTRAINT chk_email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);
-- Index for faster lookups on email and username
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_users_username ON users (username);
-- -----------------------------------------------------
-- Table `projects`
-- Stores information about projects.
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS projects (
project_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
project_name VARCHAR(100) NOT NULL,
description TEXT,
owner_id UUID NOT NULL,
start_date DATE,
end_date DATE,
status VARCHAR(20) NOT NULL DEFAULT 'Planning', -- e.g., 'Planning', 'Active', 'Completed', 'On Hold'
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_projects_owner
FOREIGN KEY (owner_id)
REFERENCES users (user_id)
ON UPDATE CASCADE
ON DELETE RESTRICT, -- Prevent deleting a user who owns projects
CONSTRAINT chk_project_dates CHECK (start_date <= end_date)
);
-- Index for faster lookups on owner_id and project_name
CREATE INDEX idx_projects_owner_id ON projects (owner_id);
CREATE INDEX idx_projects_name ON projects (project_name);
-- ------------------------------------------------
This section provides a detailed description of each table, including its purpose, column definitions, constraints, and relationships.
Customers * customer_id (INT, PK, AUTO_INCREMENT): Unique identifier for each customer.
* first_name (VARCHAR(100), NOT NULL): Customer's first name.
* last_name (VARCHAR(100), NOT NULL): Customer's last name.
* email (VARCHAR(255), UNIQUE, NOT NULL): Customer's unique email address, used for login and communication.
* phone_number (VARCHAR(20), NULLABLE): Customer's contact phone number.
* address_line1 (VARCHAR(255), NULLABLE): Primary street address.
* address_line2 (VARCHAR(255), NULLABLE): Secondary address information (e.g., apartment, suite).
* city (VARCHAR(100), NULLABLE): City of residence.
* state (VARCHAR(100), NULLABLE): State/Province of residence.
* postal_code (VARCHAR(20), NULLABLE): Postal or ZIP code.
* country (VARCHAR(100), NULLABLE): Country of residence.
* registration_date (TIMESTAMP, DEFAULT CURRENT_TIMESTAMP): Timestamp when the customer registered.
* last_login (TIMESTAMP, NULLABLE): Timestamp of the customer's last login.
PRIMARY KEY (customer_id), UNIQUE (email), idx_customers_email * Parent to Orders (1:M): A customer can place multiple orders.
Products * product_id (INT, PK, AUTO_INCREMENT): Unique identifier for each product.
* name (VARCHAR(255), NOT NULL): Name of the product.
* description (TEXT, NULLABLE): 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): Current available stock quantity.
* category (VARCHAR(100), NULLABLE): Product category (e.g., "Electronics", "Books").
* brand (VARCHAR(100), NULLABLE): Brand name of the product.
* sku (VARCHAR(50), UNIQUE, NOT NULL): Stock Keeping Unit, a unique product identifier.
* created_at (TIMESTAMP, DEFAULT CURRENT_TIMESTAMP): Timestamp when the product record was created.
* updated_at (TIMESTAMP, DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP): Timestamp of the last update to the product record.
PRIMARY KEY (product_id), UNIQUE (sku), idx_products_category, idx_products_brand * Parent to OrderItems (1:M): A product can be part of many order items.
Orders * order_id (INT, PK, AUTO_INCREMENT): Unique identifier for each order.
* customer_id (INT, NOT NULL, FK Customers): Identifier of the customer who placed the order.
* order_date (TIMESTAMP, DEFAULT CURRENT_TIMESTAMP): Date and time when the order was placed.
* total_amount (DECIMAL(10, 2), NOT NULL, CHECK >= 0): Total monetary amount of the