Relational Foundations: Data Modeling

TL;DR

Data modeling is how you design the structure of your database before you build it. You'll create a blueprint for storing information, focusing on entities, their attributes, and how they relate. This design helps ensure your database is efficient, consistent, and easy to use.

1. The Mental Model

Think of data modeling as drawing a detailed map of a city before any buildings are constructed. You're deciding where houses, schools, and roads go, and how they connect, so the city works well.

2. The Core Material

Data modeling is all about representing real-world things (entities) and their characteristics (attributes) in a structured way. The goal is to organize your data logically and efficiently. You'll primarily work with Entity-Relationship (ER) modeling, which uses diagrams to visualize these components.

2.1 Entities and Attributes

Photo by Pixabay on Pexels

An entity is a real-world object or concept that you want to store information about. Think of it as a noun: "Student," "Course," "Book." Each entity has attributes, which are properties or characteristics that describe the entity. For a "Student" entity, attributes might be "StudentID," "Name," "Email," "Major."

2.2 Relationships

Relationships define how entities interact with each other. For example, a "Student" enrolls in a "Course." Relationships have a cardinality, which specifies how many instances of one entity can relate to instances of another entity. Common cardinalities are:

• One-to-One (1:1): One instance of entity A relates to exactly one instance of entity B. (e.g., A "Person" might have one "Passport").
• One-to-Many (1:M): One instance of entity A relates to one or more instances of entity B. (e.g., A "Department" has many "Employees"). This is the most common type.
• Many-to-Many (M:N): Many instances of entity A relate to many instances of entity B. (e.g., "Students" can take many "Courses," and "Courses" can have many "Students").

When designing a relational database, M:N relationships are usually resolved into two 1:M relationships by introducing an associative entity (also called a junction table or bridge entity). This new entity holds the primary keys of the two related entities and often has its own attributes related to the relationship itself (e.g., "EnrollmentDate" for a Student-Course relationship).

2.3 Primary and Foreign Keys

Photo by Jan van der Wolf on Pexels

A primary key (PK) is an attribute (or set of attributes) that uniquely identifies each record in an entity. It must be unique and cannot be null. A foreign key (FK) is an attribute in one entity that refers to the primary key in another entity. Foreign keys establish and enforce relationships between entities.

Here's an example of an ER Diagram for a simple university system:

erDiagram
    STUDENT ||--o{ ENROLLMENT : "has"
    COURSE ||--o{ ENROLLMENT : "includes"
    DEPARTMENT ||--o{ COURSE : "offers"
    STUDENT {
        VARCHAR StudentID PK
        VARCHAR FirstName
        VARCHAR LastName
        VARCHAR Email
    }
    COURSE {
        VARCHAR CourseID PK
        VARCHAR Title
        INT Credits
        VARCHAR DepartmentID FK
    }
    ENROLLMENT {
        VARCHAR StudentID PK,FK
        VARCHAR CourseID PK,FK
        DATE EnrollmentDate
        VARCHAR Grade
    }
    DEPARTMENT {
        VARCHAR DepartmentID PK
        VARCHAR DeptName
        VARCHAR Location
    }

2.4 Normalization

Normalization is a process of organizing the columns and tables of a relational database to minimize data redundancy and improve data integrity. It involves breaking down larger tables into smaller, less redundant tables and defining relationships between them. Common normal forms include:

• 1st Normal Form (1NF): Each table cell must contain a single value, and each record must be unique. No repeating groups.
• 2nd Normal Form (2NF): Must be in 1NF, and all non-key attributes must be fully dependent on the primary key. (No partial dependencies).
• 3rd Normal Form (3NF): Must be in 2NF, and all non-key attributes must depend only on the primary key, not on other non-key attributes. (No transitive dependencies).

3. Worked Example

Let's model a simplified online store. We need to track Customers, Products, and Orders.

1. Identify Entities:

   • Customer
   • Product
   • Order

2. Identify Attributes for each Entity:

   • Customer: CustomerID (PK), FirstName, LastName, Email, Address
   • Product: ProductID (PK), Name, Description, Price
   • Order: OrderID (PK), OrderDate, CustomerID (FK)

3. Identify Relationships and Cardinality:

   • A Customer can place many Orders. An Order is placed by one Customer. (1:M Customer to Order)
   • An Order can contain many Products. A Product can be in many Orders. (M:N Order to Product)

4. Resolve M:N Relationships: The M:N relationship between Order and Product needs an associative entity. Let's call it OrderItem.

   • OrderItem attributes: OrderItemID (PK - or composite PK of OrderID+ProductID), OrderID (FK), ProductID (FK), Quantity, UnitPriceAtTimeOfOrder.

This gives us the following tables and relationships:

• Customer (CustomerID PK, FirstName, LastName, Email, Address)
• Product (ProductID PK, Name, Description, Price)
• Order (OrderID PK, OrderDate, CustomerID FK)
• OrderItem (OrderItemID PK, OrderID FK, ProductID FK, Quantity, UnitPriceAtTimeOfOrder)

Customer.CustomerID relates to Order.CustomerID (1:M).
Order.OrderID relates to OrderItem.OrderID (1:M).
Product.ProductID relates to OrderItem.ProductID (1:M).

4. Key Takeaways

• Data modeling is the crucial first step in database design, creating a blueprint for your data.
• Entities represent real-world objects, and attributes describe their properties.
• Relationships define how entities interact, with cardinality indicating how many instances relate.
• Primary keys uniquely identify records, while foreign keys link tables to establish relationships.
• Resolving many-to-many relationships with associative entities is a common and important technique.
• Normalization helps reduce data redundancy and improve data integrity, typically aiming for 3NF.

• ER diagrams visually represent your data model, making it easier to understand and communicate.

• Common Mistakes to Avoid:

  • Not identifying all necessary entities and attributes upfront, leading to redesigns later.
  • Incorrectly determining relationship cardinalities, which affects foreign key placement.
  • Forgetting to resolve many-to-many relationships, which isn't directly supported in relational databases.
  • Not choosing appropriate primary keys (e.g., using non-unique or mutable attributes).

5. Now Try It

Design an ER model for a library system. You need to track Books, Authors, and Borrowers. Books can have multiple authors, and authors can write multiple books. Borrowers can borrow multiple books, but a book can only be borrowed by one borrower at a time. Include relevant attributes for each entity. Draw out your entities, their primary keys, foreign keys, and show the relationships (including any associative entities needed).

What success looks like: You'll have clearly defined tables for Book, Author, Borrower, and an AuthoredBy (or similar) table to handle the many-to-many relationship between books and authors. You'll correctly identify primary and foreign keys for each table, and define the 1:M relationship between Borrower and Book (for books currently borrowed).