What Is Low-Level Design and Why Does It Matter?
If High-Level Design is the city map where roads go, where hospitals are, how neighborhoods connect then Low-Level Design is the plumbing blueprint. It answers the question: inside each building, how is the water piped? Where are the electrical outlets? What are the pipe diameters and pressure specs?
In software, Low-Level Design (often called Schema Design or Data Modeling) answers: what does the data inside this system actually look like? Which tables exist in the database? What columns does each table have? What type is each column? How do tables relate to each other? What rules prevent bad data from being stored?
This matters enormously. A poorly designed schema becomes an anchor that drags down every query, every feature, every API. A well-designed schema makes queries fast, data consistent, and future features easy to add.
The Library Analogy: Tables are Shelves, Rows are Books
A database is like a library. Each genre (fiction, science, history) gets its own shelf section. That section is a table. Each book on the shelf is a row. Each attribute of the book title, author, ISBN, page count is a column.
When you want a specific book, you look it up by its unique identifier (the ISBN that is the primary key). When you want all books by the same author, you look up by the author column (which has an index like an author card catalog). When you want to know which library user currently has the book, you check a separate checkouts table that links users to books (that is a foreign key relationship).
Tables: The Foundation
A table stores data for one entity one real-world thing. Identify your entities first. For Instagram: users, posts, likes, follows, comments. For an e-commerce site: users, products, categories, orders, order_items, reviews. Each gets its own table.
Data Types: Choosing the Right Container
Every column has a type. Choosing wrong costs storage, causes bugs, or prevents correct queries. Here is the complete guide:
SMALLINT: Small whole numbers (-32,768 to 32,767). Use for: age, rating out of 100, number of rooms in a hotel.
INTEGER: Whole numbers up to ~2.1 billion. The default choice for counts, quantities.
BIGINT / BIGSERIAL: Up to 9.2 quintillion. Use for IDs in large systems (avoid running out at 2B users). BIGSERIAL auto-increments.
DECIMAL(precision, scale): Exact decimal numbers. DECIMAL(10,2) stores 12345678.90. ALWAYS use this for money NEVER FLOAT for money (floating point rounding causes 0.1 + 0.2 ≠ 0.3 bugs).
VARCHAR(N): Variable-length text up to N bytes. Use for bounded strings: email (255), username (30), title (200).
TEXT: Unbounded text. Use for: descriptions, content, bios, JSON-like notes. No performance penalty vs VARCHAR in PostgreSQL.
BOOLEAN: True or false. Use for flags: is_active, is_verified, is_deleted.
TIMESTAMP: Date + time, stored in UTC. Use for created_at, updated_at, expires_at, published_at.
DATE: Date only, no time. Use for: birthdays, anniversary dates.
UUID: 128-bit universally unique identifier. Use when: IDs must be globally unique across distributed systems, or you do not want guessable sequential IDs.
JSONB: Binary JSON stored efficiently with indexing. Use for: flexible metadata, feature flags, user settings that differ per user.
ARRAY: PostgreSQL supports arrays of any type. Use for: tags (text[]), scores (integer[]).
Primary Key (PK): Every Row's Unique Identity
A primary key is a column (or combination of columns) that uniquely identifies every row. No two rows can have the same PK value. PostgreSQL automatically creates an index on the PK, making lookups by ID extremely fast.
Rule: Every table gets a primary key. No exceptions. Tables without a PK cannot be reliably referenced by other tables, cannot be efficiently updated, and are a disaster waiting to happen.
Key Characteristics of a Primary
Unique: No duplicate values are allowed in the column.
NOT NULL: It cannot contain empty or NULL values; every row must have an established identifier.
One Per Table: A table can have only one primary key constraint, though it can span multiple columns.
Foreign Key (FK): The Link Between Tables
A foreign key is a column (or set of columns) in a relational database table that links two tables together. It acts as a cross-reference by pointing to the primary key of another table, enforcing a rule (referential integrity) that restricts values in the foreign key column to those that already exist in the parent table.
Why Use Foreign Keys?
Data Consistency: Prevents invalid data from being entered. For example, a user cannot assign an employee to a department that does not exist in the department table.
Relationship Building: Clearly connects tables together, making it easy to fetch related information (e.g., matching a customer's ID to their orders).
Controlled Deletions/Updates: Prevents records from being deleted in a parent table if a child table still relies on them, or allows actions like
CASCADEto automatically delete related rows
ON DELETE Behavior, What Happens When the Parent Is Deleted?
When a user account is deleted, what happens to their posts? You must decide:
RESTRICT (default): Block the deletion of the user if any posts exist. Safe but inflexible.
CASCADE: Automatically delete all the user's posts when the user is deleted. Useful for child data (comments, likes, sessions).
SET NULL: Set the FK column to NULL when the parent is deleted. Useful when child records should survive but parent reference is optional.
SET DEFAULT: Set the FK column to its default value when parent is deleted.
Constraints: Rules the Database Enforces Automatically
Constraints are the database's bouncers. Bad data tries to get in, constraints throw it out. Always prefer database-level constraints over application-level-only validation the database is the last line of defense.
NOT NULL: Column must have a value. Cannot be empty. Use for all required fields.
UNIQUE: No two rows may have the same value in this column. Perfect for: email, username, API keys.
CHECK: Custom expression that must be true. CHECK (price > 0). CHECK (rating BETWEEN 1 AND 5). CHECK (status IN ('active','inactive','banned')).
DEFAULT: Automatic value when the column is omitted on INSERT. DEFAULT NOW() for timestamps. DEFAULT TRUE for booleans.
NOT NULL + UNIQUE combined: The column is required AND must be unique. Standard for email and username fields.
Compound UNIQUE Constraints, One Rule Across Multiple Columns
Sometimes uniqueness involves a combination of columns. One user can like a post exactly once. That is not a unique user_id (they can like many posts) or a unique post_id (many users can like it) it is a unique combination.
Indexes: Your Database's Speed Secret
An index is like a book's index page. Without it, to find every occurrence of 'photosynthesis' you would read every page of the book. With the index, you turn to the last few pages and find 'photosynthesis: pages 47, 112, 289.' You jump directly to those pages.
Without a database index on the email column, querying 'SELECT * FROM users WHERE email = ''ada@example.com''' scans every row in the users table billions of rows = minutes. With an index, the DB jumps directly to matching rows microseconds.
Index Trade-offs
Indexes speed up reads (SELECT, JOIN, WHERE, ORDER BY) dramatically.
Indexes slow down writes (INSERT, UPDATE, DELETE) slightly the index must be updated on every write.
Indexes consume storage (an index can be as large as the table it indexes in some cases).
Index every foreign key column. Without it, every JOIN to that table does a full table scan.
Do not mindlessly index everything. Each index has a cost. Add indexes for proven query patterns.
Relationships: How Tables Talk to Each Other
One-to-Many (1:N) The Most Common Relationship
One user writes many posts. One order contains many order items. One category has many products. The foreign key always goes on the 'many' side.
Many-to-Many (M:N) The Junction Table
A user can follow many users. Any user can be followed by many others. You cannot put a FK on either table alone you need a junction table (also called join table or bridge table) that holds pairs.
One-to-One (1:1)
One user has exactly one billing address. The FK with UNIQUE constraint creates a 1:1 relationship. Use this pattern to split wide tables (100+ columns) into logical groups for clarity and performance.
Normalization: Designing Data Without Redundancy
Normalization removes duplicate data. If the same data appears in two places and you update one, the other becomes wrong. That is called data anomaly and it is one of the most painful bugs to track down in production.
1. First Normal Form (1NF)
For a table to be in 1NF, it must meet the following criteria:
Atomic values: Each column must contain only single, indivisible values (no arrays, comma-separated lists, or multiple values in a single cell).
Unique column names: Each attribute must have a unique name.
Order doesn't matter: The order of rows or columns shouldn't affect the data meaning.
Primary Key: The table must have a primary key to uniquely identify each row.
2. Second Normal Form (2NF)
To achieve 2NF, a table must:
Be in 1NF.
Have no partial dependencies. This means every non-prime attribute (a column that isn't part of the primary key) must be fully dependent on the entire primary key.
This rule only applies when you have a composite primary key (a key made of multiple columns). If a non-key column depends on only part of that composite key, it must be moved to a separate table.
3. Third Normal Form (3NF)
To achieve 3NF, a table must:
Be in 2NF.
Have no transitive dependencies. This means a non-prime attribute cannot depend on another non-prime attribute. Every non-key column must depend only on the primary key.
4. Boyce-Codd Normal Form (BCNF)
BCNF is a stricter, stronger version of 3NF, often called 3.5NF. A table is in BCNF if:
It is in 3NF.
For every functional dependency $X \rightarrow Y$, $X$ must be a super key.
In plain terms, an anomaly can still occur in 3NF if a table has multiple overlapping candidate keys. BCNF ensures that a non-key attribute cannot determine any part of a candidate key.
5. Fourth Normal Form (4NF)
To achieve 4NF, a table must:
Be in BCNF.
Have no multi-valued dependencies.
A multi-valued dependency occurs when the presence of one or more rows in a table implies the presence of one or more other rows. It happens when a single primary key independent tracker points to two or more independent multi-valued relationships.
6. Fifth Normal Form (5NF) / Project-Join Normal Form (PJNF)
To achieve 5NF, a table must:
Be in 4NF.
Not be able to be decomposed into smaller tables without losing data or introducing redundancy when those tables are joined back together (lossless join).
It deals with cases where information can be reconstructed from smaller pieces, but the relationships are cyclic (e.g., A is related to B, B is related to C, and C is related to A). If breaking the table into three separate tables prevents redundancy and joining them back yields the exact original data, the original table was not in 5NF.
The Complete E-Commerce Schema: Worked Example
How to Practice LLD in ScaleDojo's Schema Design Lab
The Schema Design Lab has 50 levels organized across 5 movie-themed acts. Each level gives you a story brief and asks you to design the schema:
Act 1 (Titanic): Passenger records, bunks, ticket classes, crew. Intro levels covering tables, types, PKs, simple FKs.
Act 2 (The Social Network): Users, friendships (M:N), posts, comments, feeds. Teaches M:N junctions, composite keys, self-referential.
Act 3 (Wolf of Wall Street): Trades, portfolios, brokers, commissions, compliance. Financial schemas with DECIMAL precision, audit fields, status enums.
Act 4 (Interstellar): Mission logs, sensor readings, astronaut assignments, time-series. Advanced types, partitioning concepts, large volumes.
Act 5 (The Matrix): Multi-tenant platforms, RBAC (roles, permissions), neural network training metadata. Enterprise patterns.
Read the scenario brief carefully. Identify the entities mentioned.
Create tables for each entity. Add appropriate columns and types.
Set the primary key on every table.
Identify relationships. Add FKs on the 'many' side of 1:N. Create junction tables for M:N.
Add UNIQUE, NOT NULL, and CHECK constraints.
Add indexes on all FK columns and frequently queried columns.
Submit. Review the diff showing what you matched or missed.
The Most Common Schema Design Mistakes
Storing money as FLOAT: 0.1 + 0.2 = 0.30000000000000004 in floating point. Always use DECIMAL(10,2) or store cents as INTEGER.
Missing indexes on FK columns: Every JOIN without an index on the FK column becomes a full table scan.
Storing comma-separated lists in one column: tags = 'python,java' violates 1NF. Cannot query, cannot index, cannot constrain. Use a separate table.
No timestamps: You always need created_at and usually updated_at. Without them, debugging production issues becomes nearly impossible.
Storing passwords as plain text: Never. Hash with bcrypt or argon2. The password_hash column stores the hash, never the raw password.
Forgetting ON DELETE behavior: When a parent row is deleted, what happens to children? Undefined FK behavior leads to orphan data or blocked deletes.
Not snapshotting the price in order_items: If a product's price changes, old orders should not be affected. Store unit_price at order time.
Continue Reading
API Design In Depth: /blogs/api-design-complete-beginner-blueprint-rest-to-production
How All Three Connect: /blogs/how-hld-lld-and-api-design-work-together
Your 7-Day Practice Plan: /blogs/your-first-7-days-in-scaledojo-beginner-hand-holding-plan
A well-designed schema is invisible queries are fast, constraints prevent bad data, and new features slot in naturally. A badly designed schema is an anchor. Design the data first.