丁久

Posted on May 17 • Originally published at dingjiu1989-hue.github.io

Schema Design Patterns: Normalization, Denormalization, Naming Conventions

#database #backend #data

This article was originally published on AI Study Room. For the full version with working code examples and related articles, visit the original post.

Schema Design Patterns: Normalization, Denormalization, Naming Conventions

Schema design is the foundation of application performance and maintainability. Good schemas are intuitive, performant, and resilient to change. This article covers normalization trade-offs, denormalization strategies, and practical naming conventions.

Normalization

Normalization eliminates data redundancy through a series of normal forms. Most production schemas aim for Third Normal Form (3NF).

First Normal Form (1NF)

Each column contains atomic values. No repeating groups or arrays.

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Violates 1NF: multi-valued column

CREATE TABLE orders (

id INTEGER PRIMARY KEY,

product_ids TEXT -- '1,2,3' as comma-separated values

);

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- 1NF compliant: separate rows per product

CREATE TABLE order_items (

order_id INTEGER REFERENCES orders(id),

product_id INTEGER REFERENCES products(id),

quantity INTEGER NOT NULL,

PRIMARY KEY (order_id, product_id)

);

Second Normal Form (2NF)

1NF + every non-key column depends on the whole primary key (relevant for composite keys):

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Violates 2NF: product_name depends only on product_id, not on order_id

CREATE TABLE order_items (

order_id INTEGER,

product_id INTEGER,

product_name TEXT, -- depends on product_id only

quantity INTEGER,

PRIMARY KEY (order_id, product_id)

);

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- 2NF compliant: product_name moved to products table

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT);

Third Normal Form (3NF)

2NF + no transitive dependencies (non-key columns depend only on the primary key):

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Violates 3NF: category_name depends on category_id, not on order_id

CREATE TABLE products (

id INTEGER PRIMARY KEY,

name TEXT,

category_id INTEGER,

category_name TEXT -- transitively depends on category_id

);

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- 3NF compliant

CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, category_id INTEGER);

Denormalization

Denormalization intentionally adds redundancy for performance. Use it judiciously.

Read-Optimized Denormalization

\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\-- Normalized form

SELECT COUNT(*) FROM orders WHERE user_id = 42;

\\\\\\\\\\\\\\\\\\\\\\\\\

Read the full article on AI Study Room for complete code examples, comparison tables, and related resources.

Found this useful? Check out more developer guides and tool comparisons on AI Study Room.

DEV Community

Schema Design Patterns: Normalization, Denormalization, Naming Conventions

Schema Design Patterns: Normalization, Denormalization, Naming Conventions