[Data Architecture] Data Modeling

About this article

As the third installment of the “Data Architecture” category in the series “Architecture Crash Course for the Generative-AI Era,” this article explains data modeling.

DBs outlive code, and changing the structure of a table grown to tens of millions of rows can require hours of downtime. The first round of modeling stays effective for a decade. This article covers the 3 stages of conceptual/logical/physical modeling, normalization and denormalization, primary-key design, indexes, schema-change strategy, and soft delete and history management - presenting how to build “schemas readable by both AI and humans.”

What is data modeling in the first place

In a nutshell, data modeling is “deciding the organizational rules for the data your application handles.”

Imagine packing for a move. If you just shove clothes, dishes, and documents into cardboard boxes, you won’t know where anything is at the new place. But if you set classification rules - “clothes go in garment cases, dishes in cushioned boxes, documents in file boxes” - anyone can quickly retrieve what they need. Data modeling works the same way: it’s the process of designing “in what structure and with what relationships to store” the information handled by the business. If this design is sloppy, you’ll later face slow searches, inability to aggregate, and changes breaking everything.

What data modeling handles

Once a DB is in operation, table-structure changes are extremely high-cost. Adding even one column to a table with tens of millions of rows can require hours of downtime, and modeling mistakes become long-term debt.

Apps can be rewritten. Data models cannot be rewritten - that’s reality.

Why modeling matters most

1. You must capture the essence of the business

Rather than just lining up “columns shown on screen,” you need to translate business rules and relationships into structure. Get this wrong and you can’t keep up with later business changes.

2. Impact ripples to surrounding systems

Business DBs are referenced from many directions - other systems, analytics platforms, reports, BI. The impact range of schema changes is wider than imagined, and careful design is needed.

3. Performance characteristics are decided in design

Decisions made at the modeling stage - indexes, normalization level, partition strategy - decide the future performance ceiling. Reaching back later requires major rewrites.

The 3 stages of modeling

Data modeling proceeds in 3 stages as a rule. Diving straight into table definitions (physical design) means you can’t grasp the business essence and is the source of breakdowns.

Large-scale projects clearly separate these 3 stages, but in small/mid-scale projects, combining logical and physical is realistic. Still, always do the conceptual model first.

What is normalization

Normalization is a design principle for eliminating data duplication and preventing inconsistency on update. It’s a theory proposed by E.F. Codd in the 1970s, with normal forms from 1NF to 5NF. In practice, 3NF is general; beyond that is theoretical/academic territory.

For example, writing a customer’s name into the “orders table” means updating all order records when the customer’s name changes. Splitting it into “make a customer table separately, the order only holds customer ID” is normalization. You build a state where one change keeps the whole consistent.

In practice, 3NF is enough. 4NF and 5NF are theory.

The thinking behind denormalization

Normalization excels at consistency, but has the side effect of JOINs increasing and read performance dropping. The deliberate move to “leave redundancy on purpose” is denormalization. In analytics DBs, denormalization is standard, and in business DBs it’s applied partially where read performance is needed.

The basic motion for business DBs is design at 3NF, then denormalize only where needed - and “denormalize from the start” is dangerous.

Star schema (for analytics DBs)

The most-used analytics-DB schema is the star schema. With a “fact” table at the center and “dimension” tables around it - a star shape - it’s optimized for fast aggregation queries.

For sales analysis, for instance:

• Fact table: sales line items (timestamp, product ID, customer ID, amount)
• Dimension tables: product, customer, date, store

In this shape, multi-axis aggregation like “sales by region, month, product category” runs extremely fast. Almost all analytics platforms - BigQuery, Snowflake, Tableau - assume this shape.

Business DB: 3NF. Analytics DB: star schema. Change shape per use case.

Primary key design

The primary key is the ID uniquely identifying each record, and the choice strongly affects later design. In practice, auto-numbered surrogate keys (sequential, UUID) are mainstream, and the iron rule is to avoid using business values (email, phone) as primary keys.

If you make a business value the primary key, when that value changes, all foreign references must be updated - which can’t withstand real business changes like name change or email change.

Today, UUID v7 (RFC-finalized in 2024) is evaluated as the optimum that combines time order and randomness.

For new-DB primary keys: UUID v7 or BIGINT. v4 invites index performance degradation.

Index design

Indexes are auxiliary data structures that speed up search - correctly placed they get hundreds of times faster, but place too many and updates slow down and the DB bloats. The basic policy is “place on columns used in WHERE and JOIN, not on others.”

Composite-index order matters: only left-prefix matching works. An index of (a, b, c) works for WHERE a=?, WHERE a=? AND b=?, and WHERE a=? AND b=? AND c=?, but not for WHERE b=? alone.

Initially place the bare minimum, and add later only on actually-slow queries - that’s the rule.

Schema-change strategy (migration)

Schema changes on a running DB are the most accident-prone area. Column add/delete/type-change/constraint-change each have different danger levels, and the rule is to “change incrementally while preserving backward compatibility.”

Use a migration tool (Flyway, Liquibase, Prisma Migrate, dbt, etc.) and manage history in Git - this is required. Modifying the DB by hand is an accident factory.

Soft delete and history management

How to handle deleted records is something the design must always settle. There are roughly 3 options, decided by business requirements and legal obligations.

When personal-data deletion requests arise (GDPR etc.), physical delete becomes mandatory in some scenes. Reconciling soft delete and law requires nailing down requirements upfront.

For finance/medical/public, history tables are often legally mandatory - check business requirements.

Decision criteria

1. Business complexity

Required modeling depth varies with business complexity. Simple CRUD apps need only 3NF, but the more complex the business rules, the more you need modeling that’s conscious of DDD aggregate boundaries.

2. Scale assumption

Assumed scale affects modeling. If you’ll handle massive data in the future, consider partitioning (internally splitting one table by time/customer ID into multiple regions) and sharding (horizontal distribution across multiple DBs) at initial design.

• Under 1M records: don’t worry about anything
• 10M+: consider partitioning major tables
• 100M+: partitioning/sharding required, also consider denormalization

3. Team capability

Modeling depends on the team’s overall SQL/DB understanding. Advanced models (EAV (Entity-Attribute-Value), Polymorphic, event sourcing, etc.) become hell if the team can’t manage them.

How to choose by case

In-house admin panel / simple CRUD

Design 3NF straightforwardly. UUID v7 + soft delete + audit columns (created_at, updated_at) is enough.

EC / SaaS business DB

3NF + history retention. Hold order history and price-change history in separate tables. Prepare an analytics DB separately and sync with ETL/ELT.

Finance / medical / public sector

History tables required, physical delete forbidden. Accumulate all changes in separate tables for audit response.

Analytics platform (DWH)

Star schema. ETL business-DB data, denormalize, and load. Follow BigQuery/Snowflake best practices.

Numerical gates and index thresholds for tables

Note: Industry baseline values as of April 2026. Will become outdated as technology and the talent market shift, so requires periodic updates.

Tracking modeling quality by numbers is the modern standard. Below are the industry standard guidelines.

Tables with “10M+ rows” experience minutes-to-hours of lock during ALTER TABLE, so non-stop changes via PostgreSQL pg_repack / MySQL gh-ost or pt-online-schema-change are mandatory. Operating with the expand/contract pattern (4 stages: column add → write to both → switch → drop old column) is the standard.

Numerical gates are the DB version of ESLint / SonarQube. Mechanically checkable.

Author’s note - the full-scan hell born of “metadata JSONB”

There’s a story sometimes told about a business app where every attribute of a user profile was crammed into one JSONB (JSON Binary, the JSON storage type with fast indexed search) column called metadata. The motivation was “flexibility for future expansion,” but just filtering “users where company name contains X” started running a full scan every time, and the admin panel became unable to load as data grew.

I’ve also seen a similar site where a UI searching hundreds of thousands of users started taking 3+ seconds, and the problem only surfaced then. Even with first-aid GIN indexes, performance was far slower than normal columns, and the case is often told paired with the punchline: “we eventually spent six months on a major refactor separating major attributes into normal columns.”

A similar case: adopting UUID v4 as PK, with records growing the “physical placement of the index becoming dispersed,” and writes getting heavy - performance degradation became prominent at the tens-of-millions scale. Since 2024, UUID v7 (with time order) has been standardized, and this problem can be avoided at the design stage. The lesson common to both cases is “flexible” and “sloppy” are paper-thin apart.

Limit JSON to the partial areas where schemas fluctuate. Search targets get split into normal columns.

Data modeling pitfalls and forbidden moves

Here are the typical accidents in modeling. The schema is the DB’s skeleton, so it’s the most expensive area to fix later.

The 2024 RFC-finalization of UUID v7 (RFC 9562) is a new version that combines UUID v4’s randomness with timestamps - v7 is standard for new projects. Index performance degradation in legacy systems that adopted UUID v4 was a frequently reported problem in the 2020s.

Foreign-key constraints are the last bastion of integrity. Cutting them for performance is a textbook bad move.

AI decision axes

1. Business DB at 3NF — 4NF+ is academic; stop at 3NF and secure JOIN performance.
2. UUID v7 + soft delete + audit columns — the standard set for post-2024 new DBs.
3. Natural English naming + comments — raise AI generation accuracy, prepare for future Text-to-SQL.
4. Manage migrations in Git — keep history traceable via Flyway / Prisma Migrate.

English naming + COMMENTs decisively change AI-generated SQL accuracy

When table names use natural English like users, orders, order_items, and each column has COMMENTs (“order confirmed date,” “tax-included amount,” etc.), AI can generate accurate queries via Text-to-SQL. With abbreviated naming like tbl_001 or kbn_cd, AI can’t infer column meanings and SQL becomes inaccurate.

Explicit foreign key constraints help AI generate JOINs

When foreign key constraints are explicit in the DB, AI accurately grasps inter-table relationships and doesn’t mistake JOIN conditions. When relationships are managed only in app code without constraints, AI must guess “which table to JOIN,” increasing the possibility of writing incorrect JOIN conditions.

What to decide - what is your project’s answer?

For each of the following, try to articulate your project’s answer in 1-2 sentences. Starting work with these vague always invites later questions like “why did we decide this again?”

• Normalization level (3NF as base)
• PK strategy (UUID v7 recommended)
• Soft-delete policy (deleted_at column / history table)
• Audit columns (created_at, updated_at, created_by)
• Naming convention (English snake_case is standard)
• Migration tool (Flyway, Prisma Migrate, etc.)
• Initial set of indexes

Related Articles

https://en.senkohome.com/arch-intro-data-overview/ https://en.senkohome.com/arch-intro-data-platform/ https://en.senkohome.com/arch-intro-index-data/

Summary

This article covered data modeling, including the 3 conceptual/logical/physical stages, 3NF and denormalization, primary-key design, indexes, schema-change strategy, soft delete, and history management.

Business DBs at 3NF, UUID v7 + soft delete + audit columns as the standard set, raise AI generation accuracy with natural English naming. That is the practical answer for data modeling in 2026.

Next time we’ll cover data platforms (DWH, data lake, BI integration).

Back to series TOC -> ‘Architecture Crash Course for the Generative-AI Era’: How to Read This Book

I hope you’ll read the next article as well.