Understanding Relationships: Mastering Cardinality in Database Design

Previously in this course, we discussed Defining Entities and Attributes in Data Modeling to identify the "what" of our SaaS project. Now, we need to define the "how": how these entities interact.

In database modeling, we don't just store isolated buckets of data; we define how those buckets relate to one another. Identifying these links accurately is the difference between a flexible, scalable system and one that breaks under the weight of real-world business requirements.

The Three Pillars of Cardinality

When we talk about relationships in an ERD (Entity Relationship Diagram), we are talking about cardinality. Cardinality defines the numerical constraints of the relationship between two entities.

There are three primary types you will encounter in almost every SaaS application:

1. One-to-One (1:1)

A record in Table A is associated with exactly one record in Table B, and vice-versa.

• Business Example: A User has one Profile. While you could put all profile data in the user table, keeping them separate is often done for security or to keep the main user table lightweight.

2. One-to-Many (1:M)

A record in Table A can be associated with multiple records in Table B, but a record in Table B is associated with only one record in Table A. This is the most common relationship in relational databases.

• Business Example: An Account (or Company) has many Users. A single user typically belongs to only one company in a standard SaaS setup.

3. Many-to-Many (M:N)

Records in Table A can be associated with multiple records in Table B, and records in Table B can be associated with multiple records in Table A.

• Business Example: A User can have many Roles (e.g., Admin, Editor), and a Role can be assigned to many Users.

Relationship Comparison Table

---

Mapping Your SaaS Project Entities

Let's advance our SaaS project. Recall from Requirements Gathering for SaaS: Identifying Core Entities, we identified Accounts, Users, and Subscriptions.

How do they connect?

1. Account to User: An Account has many Users. (1:M)
2. Account to Subscription: An Account has one active Subscription. (1:1)
3. User to Project: A User can contribute to many Projects, and a Project can have many Users. (M:N)

Worked Example: The Logic of Links

When you design these in code, you are essentially defining the "owner" of the relationship.

• 1:M Implementation: Because an Account has many Users, the users table needs a column—an account_id—that points back to the specific account.
• M:N Implementation: Since neither table can hold a list of the other, we create a third table, a "junction table" (e.g., project_memberships), to store the pairs of IDs.

SQL
-- Example of 1:M (Account to User)
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    account_id INT, -- Foreign Key
    FOREIGN KEY (account_id) REFERENCES accounts(id)
);

-- Example of M:N (User to Project)
CREATE TABLE project_memberships (
    user_id INT,
    project_id INT,
    PRIMARY KEY (user_id, project_id)
);

---

Practice Exercise

Look at your current SaaS domain model. Pick two entities you've defined so far.

1. Write down the business rule that connects them (e.g., "A Subscription Plan can have many Features").
2. Determine the cardinality (1:1, 1:M, or M:N).
3. Sketch on a piece of paper how you would connect them: Where does the ID go? Do you need a third table?

---

Common Pitfalls to Avoid

• Over-Normalizing 1:1 Relationships: Don't split tables just because you can. If two pieces of data are almost always fetched together, consider keeping them in one table unless there is a clear security or performance reason to separate them.
• Ignoring the "Many" in M:N: Beginners often try to force a M:N relationship into a 1:M structure by adding columns like user_id_1, user_id_2. This is a major anti-pattern. Always use a junction table.
• Confusing 1:M with M:N: Always ask: "Can the child belong to more than one parent?" If the answer is yes, you are looking at a many-to-many relationship.

FAQ

Q: How do I decide if a relationship is 1:1 or 1:M? A: Look at the business rules. If a user can only have one subscription, it’s 1:1. If they could potentially upgrade or have historical subscriptions, it might actually be 1:M.

Q: Can I change a relationship later? A: Yes, but it is expensive. Changing a 1:M to an M:N requires creating new tables and migrating data. Getting the cardinality right during the database modeling phase saves hours of refactoring later.

Recap

Understanding the relationships between your entities is the backbone of Introduction to Database Design: Schema Basics for SaaS. By mastering cardinality—one-to-one, one-to-many, and many-to-many—you ensure your database schema is a reflection of your business logic.

Up next: We will look at how to visualize these connections using standard ERD notation.