Applying 1NF to the SaaS Schema: Practical Data Refactoring

Previously in this course, we covered First Normal Form (1NF) Basics: Ensuring Data Atomicity, where we defined atomicity as the requirement that every column contains only a single, indivisible value. Having established the importance of preventing data anomalies as discussed in our Introduction to Normalization: Preventing Data Anomalies, today we will perform a hands-on audit of our SaaS schema to move from a "prototype" structure to a production-ready, normalized model.

Auditing the SaaS Schema for Violations

In a fast-paced SaaS development environment, it is tempting to "shortcut" the schema. You might store a list of feature tags in a text column or concatenate multiple phone numbers into a single field to save time. These are classic 1NF violations.

When we audit our current SaaS tables, we look for three common "red flags":

1. Delimited Strings: Columns containing comma-separated lists (e.g., feature_access: "dashboard,reports,api").
2. Repeating Groups: Columns named like phone1, phone2, or tag_1, tag_2.
3. Compound Values: Fields that require the application to "parse" data before using it, such as storing City, State, Zip in one address column.

Worked Example: Refactoring Subscription Features

Let's look at our subscriptions table from Implementing Subscription Tables: SQL Constraints for SaaS. Suppose we initially designed the table with a non-atomic features column:

SQL
-- BAD: Non-atomic structure
CREATE TABLE subscriptions (
    id SERIAL PRIMARY KEY,
    account_id INT,
    plan_name VARCHAR(50),
    features TEXT -- Contains "dashboard,analytics,reporting"
);

This structure is a nightmare for SQL performance. If you want to find all accounts that have access to the "analytics" feature, you are forced to use LIKE '%analytics%', which ignores indexes and scans the entire table.

The Normalization Step

To reach 1NF, we must eliminate the list and represent the relationship as discrete rows. We remove the features column and create a dedicated table to house these values.

SQL
-- GOOD: Normalized structure
CREATE TABLE subscription_features (
    subscription_id INT REFERENCES subscriptions(id),
    feature_name VARCHAR(50),
    PRIMARY KEY (subscription_id, feature_name)
);

Now, querying for "analytics" becomes a simple index-backed lookup: SELECT subscription_id FROM subscription_features WHERE feature_name = 'analytics';

Hands-on Exercise: Audit Your User Contact Data

Look at your current users table. Do you have a column for phone_numbers or social_links?

1. Identify: Find any column that currently holds more than one piece of data or a list of items.
2. Refactor: Write the DDL to move those items into a new, separate table.
3. Verify: Ensure that each row in your new table contains only one distinct value per record.

Common Pitfalls

• Over-Normalization: Don’t split a full_name column into first and last unless your business requirements explicitly demand it for sorting or searching. Keep it "simple" until the data needs to be queried independently.
• Ignoring Data Types: 1NF is not just about structure; it’s about types. Storing a date inside a string column is a subtle 1NF violation because the database treats it as text rather than a temporal value.
• Forgetting Constraints: When you move data to a new table, ensure you apply NOT NULL and FOREIGN KEY constraints, as outlined in Constraints and Data Integrity: A Guide to SQL DDL.

FAQ

Does 1NF apply to JSONB columns in PostgreSQL? Technically, JSONB allows for nested structures, which violates the "indivisible" rule of 1NF. However, in modern SaaS architecture, we often use JSONB for truly variable, schema-less metadata. Use it for data that never needs to be joined or filtered by the database engine.

Is it always wrong to store comma-separated lists? Only if you need to query the individual items within that list. If the data is only ever read by the application as a single blob, it is not a violation of logical atomicity.

Recap

Normalization is the process of organizing data to reduce redundancy and improve integrity. By auditing our SaaS schema for delimited strings and repeating groups, we ensure our database remains performant as our user base grows. Remember: if you have to write a regex or a LIKE operator to extract an item from a column, that column is not atomic.

Up next: Normalization Review and Trade-offs