Data Modeling & Conceptual Design: ER Diagrams, Logical/Physical DB Design

What is Data Modeling?

Data modeling is the process of defining and analyzing data requirements needed to support business processes. It creates a blueprint of how data flows, stores, and relates in a system. There are three levels of data modeling:

1. Conceptual Data Model (Business View)

   • Focuses on what data is needed (entities, attributes, relationships).
   • Uses ER diagrams (Entity-Relationship models).
   • Independent of any database system.

2. Logical Data Model (System View)

   • Defines how data is structured (tables, keys, constraints).
   • Uses relational schema (e.g., Student(ID, Name, DOB)).
   • Independent of physical storage.

3. Physical Data Model (Technical View)

   • Specifies where and how data is stored (file formats, indexes, partitions).
   • Depends on the DBMS (e.g., MySQL, Oracle).

Why is Data Modeling Important?

• Clarifies business needs before coding.
• Reduces errors by validating data requirements early.
• Improves efficiency by optimizing storage and queries.
• Supports scalability (e.g., Daraz’s inventory system handles millions of products).

Conceptual Data Modeling: ER Diagrams

An ER diagram visually represents:

• Entities (e.g., Customer, Order, Product).
• Attributes (properties of entities, e.g., Customer.Name, Order.Date).
• Relationships (how entities interact, e.g., Customer PLACES Order).

Key Symbols in ER Diagrams

erDiagram
    ENTITY ||--o{ ATTRIBUTE : "has"
    ENTITY ||--o{ RELATIONSHIP : "participates in"
    RELATIONSHIP }|--|| ENTITY : "connects"
    ENTITY {
        string name
        int id
    }
    ATTRIBUTE {
        string type
        boolean is_primary_key
    }
    RELATIONSHIP {
        string type "1:1, 1:N, M:N"
        string name
    }

Worked Example: eSewa Transaction System

Scenario: Model data for eSewa’s mobile payment system. Entities:

1. User (ID, Name, Phone, Email)
2. Transaction (ID, Amount, Date, Status)
3. ServiceProvider (ID, Name, Category)

Relationships:

• A User can make many Transactions (1:N).
• A Transaction is for one ServiceProvider (N:1).

erDiagram
    USER ||--o{ TRANSACTION : "makes"
    TRANSACTION ||--|| SERVICE_PROVIDER : "pays for"
    USER {
        int user_id PK
        string name
        string phone
    }
    TRANSACTION {
        int transaction_id PK
        decimal amount
        date timestamp
        string status
    }
    SERVICE_PROVIDER {
        int provider_id PK
        string name
        string category
    }

Cardinality and Relationship Types

Cardinality defines how many instances of one entity relate to another. Common types:

Note: M:N relationships are always resolved by creating a junction entity (e.g., Enrollment for Student-Course).

Logical Database Design: Tables and Keys

Step 1: Convert ER Diagram to Relational Schema

For the eSewa example:

1. User → User(user_id, name, phone, email)
2. Transaction → Transaction(transaction_id, user_id, amount, date, status)
3. ServiceProvider → ServiceProvider(provider_id, name, category)

Step 2: Define Keys

• Primary Key (PK): Uniquely identifies a record (e.g., user_id).
• Foreign Key (FK): Links to a PK in another table (e.g., user_id in Transaction references User.user_id).
• Composite Key: Multiple columns as PK (e.g., Student_ID + Course_ID in Enrollment).

Step 3: Normalization

Normalization reduces data redundancy and anomalies (update, insert, delete issues). Follow these normal forms:

Worked Example: Normalizing Daraz’s Order System Unnormalized Table: Order(order_id, customer_name, product1, product2, total_amount)

3NF Tables:

1. Customer(customer_id, name, address)
2. Product(product_id, name, price)
3. Order(order_id, customer_id, date, total_amount)
4. OrderItem(order_id, product_id, quantity)

Physical Database Design

Key Decisions:

1. Storage Engine: InnoDB (ACID compliance) vs. MyISAM (faster reads).
2. Indexing: Add indexes on FK columns (e.g., user_id in Transaction).
3. Partitioning: Split large tables by date (e.g., Transaction_2023, Transaction_2024).
4. Data Types: Use VARCHAR(50) for names, DECIMAL(10,2) for money.

Example: Ncell’s Customer Database

CREATE TABLE Customer (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(15) UNIQUE,
    join_date DATE,
    INDEX idx_phone (phone)
);

CREATE TABLE Subscription (
    subscription_id INT PRIMARY KEY,
    customer_id INT,
    plan_type ENUM('Basic', 'Premium', 'Unlimited'),
    start_date DATE,
    end_date DATE,
    FOREIGN KEY (customer_id) REFERENCES Customer(customer_id),
    INDEX idx_plan (plan_type)
);

In the Real World

1. eSewa

   • Conceptual Model: ER diagram with User, Transaction, and ServiceProvider.
   • Logical Design: Tables for transactions with user_id as FK.
   • Physical Design: Indexes on transaction_id and date for fast queries.

2. Daraz

   • M:N Relationship: Customer to Product via OrderItem junction table.
   • Normalization: Separates Product (name, price) from Inventory (stock, location).

3. NEPSE (Nepal Stock Exchange)

   • Cardinality: Trader (1) to Trade (N) to Share (1).
   • Physical Design: Partitioned tables by trade_date for performance.

Exam Tip

1. ER Diagrams: Always show entities, attributes, and relationships with correct cardinality. Label PKs and FKs.
2. Normalization: Questions often ask to convert unnormalized tables to 3NF. Practice with real examples (e.g., bank accounts, hospital records).
3. Logical vs. Physical Design:
   • Logical = "What tables do we need?" (e.g., Student, Course).
   • Physical = "How do we store them?" (e.g., indexes, partitioning).
4. Worked Examples: For questions like "Design a database for a retail store," start with an ER diagram, then normalize, then write SQL CREATE TABLE statements.
5. Common Pitfalls:
   • Forgetting to resolve M:N relationships with junction tables.
   • Not defining PKs/FKs in logical design.
   • Ignoring data types in physical design (e.g., using VARCHAR for numbers).

ER diagram symbols: rectangle (entity), oval (attribute), diamond (relationship) (Image: Jarfuls of Tweed, CC BY-SA 4.0, via Wikimedia Commons)

Physical storage for databases (e.g., Ncell’s servers) (Image: Federal Bureau of Investigation, Public domain, via Wikimedia Commons)