Mapping SaaS Users and Accounts: Entity Design for Scalability

Previously in this course, we explored Introduction to ERD Notation: Visualizing Your Data Model to translate our abstract requirements into visual diagrams. Now, we move from the whiteboard to the foundation of our project: the User-Account relationship.

In any SaaS application, the most critical design decision you'll make is how users interact with the businesses (accounts) that pay for the service. Mismanaging this relationship leads to "spaghetti data," where users are locked into single accounts or, worse, security leaks where data crosses tenant boundaries.

The SaaS Modeling Paradigm

In modern SaaS, we distinguish between an Identity (the person) and a Tenant (the business entity).

• Accounts (Tenants): The organizational unit that signs the contract and pays the bill.
• Users: The actual people accessing the software.

The relationship is almost always One-to-Many (One Account has many Users). However, to be future-proof, you should consider that a single user might eventually need to belong to multiple accounts (e.g., a consultant working for three different client agencies). For now, we will stick to the standard One-to-Many model to keep our foundation stable.

Refining Attributes for Authentication

When defining our entities, we must separate identity from profile. An authentication system only cares about what is needed to verify a user; an application profile cares about who they are.

The Account Entity

The User Entity

Worked Example: The Schema Logic

When we translate this into our project, we ensure the account_id is a mandatory foreign key on the users table. This prevents "orphaned" users who aren't associated with any billing entity.

SQL
-- Conceptual representation of our User-Account mapping
CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    account_id INT REFERENCES accounts(account_id),
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash CHAR(60) NOT NULL -- Standard for bcrypt/argon2
);

By enforcing the account_id as a NOT NULL constraint, we ensure that every user is scoped to an account from the moment they are created. This is the bedrock of multi-tenancy.

Hands-on Exercise

Grab your scratchpad or diagramming tool. Based on the entities above, add an is_admin flag to the users table.

1. Does this flag belong on the users table, or should it be a separate roles table?
2. If you put it on the users table, what happens if a user needs to be an admin for one account but a read-only viewer for another? Hint: Keep it simple for this lesson by adding a boolean column, but acknowledge that this design choice limits future flexibility.

Common Pitfalls in SaaS Modeling

• The "One-to-One" Trap: Don't create a users table that is the account. If you merge them, you'll have to duplicate data every time a second user joins the same company. Always normalize them into separate entities as discussed in Defining Entities and Attributes in Data Modeling.
• Storing Plaintext Passwords: Never store passwords. Always store a hash. Your database design should accommodate the length required by modern hashing algorithms (usually 60-128 characters).
• Assuming Global Uniqueness: Don't assume an email is unique across the entire database if you eventually want to support users in different environments. Decide early if your email constraint is global or scoped to an account.

FAQ

Q: Should I use UUIDs or Integers for my IDs? A: Use UUIDs for public-facing IDs to prevent enumeration attacks (where someone guesses the next ID in the sequence). Use Integers for internal keys if performance is your absolute priority.

Q: How do I handle users who leave a company? A: We will cover this in later lessons using "Soft Deletes," which allow you to disable access without breaking historical audit logs.

Recap

We've established the core of our SaaS data model: the accounts table as the tenant container and the users table as the identity provider. By linking these via a foreign key, we’ve created a structure that supports organizational hierarchy and secure authentication.

Up next: Designing Subscription Models — we will learn how to attach pricing and billing logic to our accounts entity.