Introduction to Database Design and ER Models

TL;DR

Database design is about organizing data efficiently and logically to meet an application's needs. Entity-Relationship (ER) models are a visual way to plan this organization by identifying key entities and their relationships. Understanding ER models helps you create a robust and well-structured database schema.

1. The Mental Model

Imagine you're organizing a library; you wouldn't just throw all the books on the floor. Database design is like creating a clear cataloging system, and ER models are your blueprints, showing how different types of books (entities) relate to their authors, publishers, and shelves.

2. The Core Material

When designing a database, you're essentially trying to capture information about the real world and store it in a structured way. This involves three main steps:

Conceptual Design (ER Model)

Photo by Ron Lach on Pexels

This is where you identify the main things (entities) you want to store information about, the characteristics (attributes) of those things, and how they connect to each other (relationships). An ER model is a high-level, abstract representation of the data requirements.

• Entities: These are real-world objects or concepts that you want to store data about. Think of nouns. Examples: Student, Course, Instructor.
• Attributes: These are properties or characteristics of an entity. Think of adjectives describing the entity. Examples for Student: StudentID, Name, Major.
• Relationships: These describe how entities are connected to each other. Examples: A Student enrolls in a Course, an Instructor teaches a Course.

Relationships have a cardinality, which specifies how many instances of one entity can be associated with how many instances of another entity.

• One-to-One (1:1): Each instance of Entity A is related to at most one instance of Entity B, and vice-versa. (e.g., A Person has one Passport).
• One-to-Many (1:N): Each instance of Entity A can be related to many instances of Entity B, but each instance of Entity B is related to at most one instance of Entity A. (e.g., a Department has many Employees).
• Many-to-Many (N:M): Each instance of Entity A can be related to many instances of Entity B, and vice-versa. (e.g., Students enroll in many Courses, and Courses are enrolled by many Students).

erDiagram
    CUSTOMER ||--o{ ORDER : places
    ORDER ||--|{ ORDER_LINE : contains
    ORDER_LINE }|--|| PRODUCT : relates_to
    PRODUCT {
        VARCHAR ProductID PK
        VARCHAR Name
        DECIMAL Price
    }
    CUSTOMER {
        VARCHAR CustomerID PK
        VARCHAR Name
        VARCHAR Address
    }
    ORDER {
        VARCHAR OrderID PK
        VARCHAR CustomerID FK
        DATE OrderDate
    }
    ORDER_LINE {
        VARCHAR OrderLineID PK
        VARCHAR OrderID FK
        VARCHAR ProductID FK
        INT Quantity
    }

Logical Design

Photo by DS stories on Pexels

After the ER model, you translate it into a specific database model, typically the relational model. This involves mapping entities to tables, attributes to columns, and relationships to foreign keys. Many-to-many relationships often require creating a new "junction" or "associative" table.

Physical Design

Photo by MART PRODUCTION on Pexels

This is where you decide on the actual implementation details, like data types for columns, indexes for performance, and storage structures. This step is often specific to the database management system (DBMS) you're using (e.g., MySQL, PostgreSQL).

3. Worked Example

Let's design a simple database for a university to track students and courses.

1. Identify Entities: We need Student and Course.
2. Identify Attributes:
   • Student: StudentID (unique identifier), Name, Email, Major.
   • Course: CourseID (unique identifier), Title, Credits.
3. Identify Relationships:
   • A Student can enroll in multiple Courses.
   • A Course can have multiple Students enrolled.
   • This is a Many-to-Many (N:M) relationship.

To handle the N:M relationship, we introduce a new entity (or associative table in the logical model): Enrollment.

• Enrollment Entity: This entity links Student and Course. It would have attributes like EnrollmentID (PK), StudentID (FK), CourseID (FK), and Grade.

This structured thinking helps ensure all necessary data is captured and linked correctly.

4. Key Takeaways

• Database design starts with understanding the real-world information you need to store.
• ER models visually represent your database's entities, their attributes, and how they relate.
• Entities are key objects, attributes are their properties, and relationships show connections between entities.
• Cardinality (1:1, 1:N, N:M) describes the nature of these relationships.
• Many-to-many relationships usually require an intermediary "junction" table in your design.
• A well-designed database reduces data redundancy and improves data integrity.

Common Mistakes to Avoid:
- Not identifying all entities: Missing crucial "things" you need to store information about.
- Confusing attributes with entities: Forgetting that an attribute describes an entity, not an entity itself.
- Incorrectly assigning cardinality: Misunderstanding whether one item relates to one, many, or vice versa.
- Ignoring many-to-many relationships: Trying to force a direct link between two entities when an intermediate table is needed.
- Skipping the conceptual design: Jumping straight to tables and columns without a clear ER model can lead to design flaws.

5. Now Try It

Design an ER model for a simple online bookstore. You'll need to keep track of Books, Authors, and Customers.

What to do:
1. List the main entities.
2. For each entity, list its key attributes.
3. Identify the relationships between your entities and determine their cardinality.
4. If you find any many-to-many relationships, propose an associative entity.

What success looks like: You have a clear list of entities with their attributes and correctly identified relationships, including any necessary associative entities for many-to-many links.