Designing and Implementing the E-commerce Database Schema

Introduction

In our previous post, we set up our Oracle database environment. Now, let's focus on designing and implementing our e-commerce database schema. A well-structured database is crucial for application performance, data integrity, and future scalability.

Database Schema Design

Entity-Relationship Modeling

The first step in database design is identifying the core entities and their relationships. For our e-commerce system, we'll focus on these primary entities:

Customers - Individuals who place orders
Products - Items available for purchase
Orders - Customer purchases
Order Items - Specific products within each order

These entities have the following relationships:

A customer can place multiple orders (one-to-many)
An order belongs to exactly one customer (many-to-one)
An order can contain multiple products (many-to-many)
A product can appear in multiple orders (many-to-many)

The many-to-many relationship between orders and products is resolved using the order_items junction table.

ER Diagram showing many-to-many relationship

Normalization Principles

Database normalization helps eliminate redundancy and improve data integrity. Let's apply these principles to our schema:

First Normal Form (1NF)

Each table cell contains a single value
Each column has a unique name
All rows in a table are unique

For example, we'll store each order item as a separate row rather than as an array within the orders table.

Second Normal Form (2NF)

Table must be in 1NF
All non-key attributes depend on the entire primary key

Our order_items table will use a composite key of order_id and product_id, with attributes like quantity depending on this complete key.

Third Normal Form (3NF)

Table must be in 2NF
No transitive dependencies (non-key attributes depend only on the key)

We'll store product information like name and price in the products table rather than duplicating this data in the order_items table.

Schema Implementation

Let's create our database schema using Oracle SQL:

-- Create Customers Table
CREATE TABLE Customers (
  Customer_ID NUMBER NOT NULL,
  First_name VARCHAR2(50) NOT NULL,
  Last_name VARCHAR2(50) NOT NULL,
  Email_name VARCHAR2(50) NOT NULL,
  Phone VARCHAR2(50) NOT NULL,
  Address VARCHAR2(100) NOT NULL,
  CONSTRAINT pk_customers PRIMARY KEY (Customer_ID)
);

-- Create Products Table
CREATE TABLE Product (
    Product_ID NUMBER PRIMARY KEY,
    Name VARCHAR2(100) NOT NULL,
    Description VARCHAR2(255),
    Price NUMBER NOT NULL
);


-- Create Orders Table
CREATE TABLE Orders (
    Order_ID NUMBER PRIMARY KEY,
    Customer_ID NUMBER NOT NULL,
    Order_Date DATE NOT NULL,
    Total_Amount NUMBER NOT NULL,
    CONSTRAINT fk_orders_customer FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID)
);

-- Create Order_Items Table
CREATE TABLE Order_Items (
    Order_Item_ID NUMBER PRIMARY KEY,
    Order_ID NUMBER NOT NULL,
    Product_ID NUMBER NOT NULL,
    Quantity NUMBER NOT NULL,
    Unit_Price NUMBER NOT NULL,
    CONSTRAINT fk_orderitems_order FOREIGN KEY (Order_ID) REFERENCES Orders(Order_ID),
    CONSTRAINT fk_orderitems_product FOREIGN KEY (Product_ID) REFERENCES Product(Product_ID)
);

OR
(Alternatively, if you prefer composite keys instead of an auto-increment Order_Item_ID:)

CREATE TABLE Order_Items (
    Order_ID NUMBER NOT NULL,
    Product_ID NUMBER NOT NULL,
    Quantity NUMBER NOT NULL,
    Unit_Price NUMBER NOT NULL,
    CONSTRAINT pk_orderitems PRIMARY KEY (Order_ID, Product_ID),
    CONSTRAINT fk_orderitems_order FOREIGN KEY (Order_ID) REFERENCES Orders(Order_ID),
    CONSTRAINT fk_orderitems_product FOREIGN KEY (Product_ID) REFERENCES Product(Product_ID)
);


CREATE TABLE Shipments (
    Shipment_ID NUMBER PRIMARY KEY,
    Order_ID NUMBER NOT NULL,
    Shipment_Date DATE NOT NULL,
    CONSTRAINT fk_shipments_order FOREIGN KEY (Order_ID) REFERENCES Orders(Order_ID)
);

Since you've created new tables (CUSTOMERS, ORDERS, PRODUCT, ORDER_ITEMS, SHIPMENTS), these won't interfere with your existing application unless:

Name Conflicts:
- If your application uses tables with the same names (CUSTOMERS, ORDERS, etc.), these new tables might overwrite or conflict with them.
- To verify, you can run:
```
SELECT table_name FROM user_tables WHERE table_name IN ('CUSTOMERS', 'ORDERS', 'PRODUCT', 'ORDER_ITEMS', 'SHIPMENTS');
```

This will confirm if these tables existed prior to your creation.

Schema Confusion:

If you're working in a different schema (or user), your app will be unaffected unless it accesses this specific schema.
Check your current schema in SQL Developer:

SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM dual;

Unintended Data Changes:
- Creating tables is non-destructive, but if you've modified existing tables or constraints (which you haven’t mentioned), it could have unintended side effects.
- Since you mentioned only creating new tables, you’re safe here.
Dependencies or Triggers:
- If any application logic dynamically references these table names (unlikely unless it's a very dynamic app), that might cause issues.
- You can check for triggers:
```
SELECT trigger_name FROM user_triggers WHERE table_name IN ('CUSTOMERS', 'ORDERS', 'PRODUCT', 'ORDER_ITEMS', 'SHIPMENTS');
```

Populating the Database

With our schema created, let's insert some sample data:

-- Insert Customers
INSERT INTO PRODUCT (Product_ID, Name, Description, Price)
VALUES (1, 'Laptop', '15-inch display, 8GB RAM, 256GB SSD', 750.00);

INSERT INTO PRODUCT (Product_ID, Name, Description, Price)
VALUES (2, 'Smartphone', '6.1-inch display, 128GB Storage', 500.00);

INSERT INTO PRODUCT (Product_ID, Name, Description, Price)
VALUES (3, 'Wireless Mouse', 'Ergonomic design, USB receiver', 25.00);

INSERT INTO PRODUCT (Product_ID, Name, Description, Price)
VALUES (4, 'Headphones', 'Noise-cancelling, over-ear', 150.00);

INSERT INTO PRODUCT (Product_ID, Name, Description, Price)
VALUES (5, 'Monitor', '24-inch Full HD', 180.00);

OR

INSERT ALL
  INTO PRODUCT (Product_ID, Name, Description, Price) VALUES (1, 'Laptop', '15-inch display, 8GB RAM, 256GB SSD', 750.00)
  INTO PRODUCT (Product_ID, Name, Description, Price) VALUES (2, 'Smartphone', '6.1-inch display, 128GB Storage', 500.00)
  INTO PRODUCT (Product_ID, Name, Description, Price) VALUES (3, 'Wireless Mouse', 'Ergonomic design, USB receiver', 25.00)
  INTO PRODUCT (Product_ID, Name, Description, Price) VALUES (4, 'Headphones', 'Noise-cancelling, over-ear', 150.00)
  INTO PRODUCT (Product_ID, Name, Description, Price) VALUES (5, 'Monitor', '24-inch Full HD', 180.00)
SELECT * FROM dual;

-- Insert more orders and items as needed


-- Insert mock data into the PRODUCT table
INSERT ALL
  INTO PRODUCT (Product_ID, Name, Description, Price) VALUES (1, 'Laptop', '15-inch display, 8GB RAM, 256GB SSD', 750.00)
  INTO PRODUCT (Product_ID, Name, Description, Price) VALUES (2, 'Smartphone', '6.1-inch display, 128GB Storage', 500.00)
  INTO PRODUCT (Product_ID, Name, Description, Price) VALUES (3, 'Wireless Mouse', 'Ergonomic design, USB receiver', 25.00)
  INTO PRODUCT (Product_ID, Name, Description, Price) VALUES (4, 'Headphones', 'Noise-cancelling, over-ear', 150.00)
  INTO PRODUCT (Product_ID, Name, Description, Price) VALUES (5, 'Monitor', '24-inch Full HD', 180.00)
SELECT * FROM dual;

-- Insert mock data into the CUSTOMERS table
INSERT ALL
  INTO CUSTOMERS (Customer_ID, First_Name, Last_Name, Email, Phone, Address) VALUES (1, 'John', 'Doe', 'john.doe@example.com', 1234567890, '123 Elm Street')
  INTO CUSTOMERS (Customer_ID, First_Name, Last_Name, Email, Phone, Address) VALUES (2, 'Jane', 'Smith', 'jane.smith@example.com', 2345678901, '456 Oak Avenue')
  INTO CUSTOMERS (Customer_ID, First_Name, Last_Name, Email, Phone, Address) VALUES (3, 'Alice', 'Johnson', 'alice.johnson@example.com', 3456789012, '789 Pine Road')
  INTO CUSTOMERS (Customer_ID, First_Name, Last_Name, Email, Phone, Address) VALUES (4, 'Bob', 'Brown', 'bob.brown@example.com', 4567890123, '101 Maple Lane')
  INTO CUSTOMERS (Customer_ID, First_Name, Last_Name, Email, Phone, Address) VALUES (5, 'Charlie', 'Davis', 'charlie.davis@example.com', 5678901234, '202 Birch Blvd')
SELECT * FROM dual;

-- Insert mock data into the ORDERS table
INSERT ALL
  INTO ORDERS (Order_ID, Customer_ID, Order_Date, Total_Amount) VALUES (1, 1, TO_DATE('2024-02-01', 'YYYY-MM-DD'), 925.00)
  INTO ORDERS (Order_ID, Customer_ID, Order_Date, Total_Amount) VALUES (2, 2, TO_DATE('2024-02-02', 'YYYY-MM-DD'), 650.00)
  INTO ORDERS (Order_ID, Customer_ID, Order_Date, Total_Amount) VALUES (3, 3, TO_DATE('2024-02-03', 'YYYY-MM-DD'), 205.00)
  INTO ORDERS (Order_ID, Customer_ID, Order_Date, Total_Amount) VALUES (4, 4, TO_DATE('2024-02-04', 'YYYY-MM-DD'), 930.00)
  INTO ORDERS (Order_ID, Customer_ID, Order_Date, Total_Amount) VALUES (5, 5, TO_DATE('2024-02-05', 'YYYY-MM-DD'), 180.00)
SELECT * FROM dual;

-- Insert mock data into the ORDER_ITEMS table
INSERT ALL
  INTO ORDER_ITEMS (Order_Item_ID, Order_ID, Product_ID, Quantity, Unit_Price) VALUES (1, 1, 1, 1, 750.00)
  INTO ORDER_ITEMS (Order_Item_ID, Order_ID, Product_ID, Quantity, Unit_Price) VALUES (2, 1, 3, 7, 25.00)
  INTO ORDER_ITEMS (Order_Item_ID, Order_ID, Product_ID, Quantity, Unit_Price) VALUES (3, 2, 2, 1, 500.00)
  INTO ORDER_ITEMS (Order_Item_ID, Order_ID, Product_ID, Quantity, Unit_Price) VALUES (4, 2, 4, 1, 150.00)
  INTO ORDER_ITEMS (Order_Item_ID, Order_ID, Product_ID, Quantity, Unit_Price) VALUES (5, 3, 3, 2, 25.00)
  INTO ORDER_ITEMS (Order_Item_ID, Order_ID, Product_ID, Quantity, Unit_Price) VALUES (6, 3, 5, 1, 180.00)
  INTO ORDER_ITEMS (Order_Item_ID, Order_ID, Product_ID, Quantity, Unit_Price) VALUES (7, 4, 1, 1, 750.00)
  INTO ORDER_ITEMS (Order_Item_ID, Order_ID, Product_ID, Quantity, Unit_Price) VALUES (8, 4, 5, 1, 180.00)
  INTO ORDER_ITEMS (Order_Item_ID, Order_ID, Product_ID, Quantity, Unit_Price) VALUES (9, 5, 5, 1, 180.00)
SELECT * FROM dual;

-- Insert mock data into the SHIPMENTS table
INSERT ALL
  INTO SHIPMENTS (Shipment_ID, Order_ID, Shipment_Date) VALUES (1, 1, TO_DATE('2024-02-02', 'YYYY-MM-DD'))
  INTO SHIPMENTS (Shipment_ID, Order_ID, Shipment_Date) VALUES (2, 2, TO_DATE('2024-02-03', 'YYYY-MM-DD'))
  INTO SHIPMENTS (Shipment_ID, Order_ID, Shipment_Date) VALUES (3, 3, TO_DATE('2024-02-04', 'YYYY-MM-DD'))
  INTO SHIPMENTS (Shipment_ID, Order_ID, Shipment_Date) VALUES (4, 4, TO_DATE('2024-02-05', 'YYYY-MM-DD'))
  INTO SHIPMENTS (Shipment_ID, Order_ID, Shipment_Date) VALUES (5, 5, TO_DATE('2024-02-06', 'YYYY-MM-DD'))
SELECT * FROM dual;

Testing and Validation

After creating our schema and inserting data, we should validate our design by testing relationships and constraints:

Test foreign key constraints - Try inserting an order with an invalid customer_id
Verify cascading operations - What happens when a customer is deleted?
Check data integrity - Ensure that our calculated fields like subtotal work correctly
Run basic queries - Test simple SELECT statements to retrieve data across relationships

Conclusion

We've now created a properly normalized database schema for our e-commerce system. This foundation will support all our future development, from complex queries to application integration.

In our next post, we'll explore SQL and PL/SQL development, including advanced queries, stored procedures, and functions that will power our e-commerce application's business logic.