A topic in the Open Knowledge Graph — a free, open map of 15,290 topics and the order to learn them in.
Database schema design translates a conceptual ER model into a concrete relational schema with tables, columns, data types, and constraints. The process involves mapping entities to tables, resolving many-to-many relationships into junction tables, choosing appropriate data types and defaults, and deciding on normalization level vs. query performance tradeoffs. Good schema design minimizes redundancy and anomalies while efficiently supporting the application's most common query patterns.
Design a complete schema for a real-world application (e.g., an e-commerce site) from requirements to DDL. Review patterns like junction tables for M:N relationships, audit logging columns (created_at, updated_at), and soft deletes.
You already know how to draw an ER diagram that captures entities and their relationships, how normalization eliminates redundancy and update anomalies, and how primary and foreign keys enforce identity and referential integrity. Schema design is where these skills converge: you take a conceptual model and produce the actual tables, columns, types, and constraints that a database engine will enforce. The quality of this translation determines whether your database is a reliable foundation or a source of perpetual bugs and performance headaches.
The core mapping rules are mechanical. Each strong entity becomes a table, with its attributes becoming columns and its primary key becoming the table's primary key. A one-to-many relationship is captured by adding a foreign key column on the "many" side pointing to the "one" side's primary key. A many-to-many relationship requires a junction table (sometimes called a bridge or associative table) that contains foreign keys to both participating tables, with the combination forming its primary key. For example, a students-to-courses enrollment relationship becomes an `enrollments` table with `student_id` and `course_id` columns, each referencing their respective tables. Optional relationships are modeled with nullable foreign keys; mandatory ones use NOT NULL constraints.
Choosing data types is more consequential than it first appears. An integer for a zip code loses leading zeros. A VARCHAR(255) for a field that will never exceed 20 characters wastes space in indexes. Using TIMESTAMP WITH TIME ZONE versus without it determines whether your application handles daylight saving time correctly. Every column should also have appropriate constraints: NOT NULL where a value is always required, UNIQUE where duplicates are invalid (like email addresses), CHECK constraints for domain rules (like `price >= 0`), and DEFAULT values for fields with sensible fallbacks (like `created_at` defaulting to the current timestamp). These constraints are your last line of defense — they catch bugs that slip past application code.
The normalization level you choose is a deliberate tradeoff, not a rigid rule. Your prerequisite knowledge of 3NF and BCNF tells you how to eliminate redundancy, but a fully normalized schema can require expensive joins for common queries. In practice, you normalize first and then selectively denormalize for performance — for example, storing a customer's name directly on an orders table to avoid joining the customers table on every order listing. The key discipline is to document every denormalization decision and accept the maintenance cost: when a customer's name changes, you now have to update it in multiple places. Schema design is the one area of software development where upfront investment pays the highest returns, because altering a table with millions of rows is expensive, slow, and risky in ways that refactoring application code is not.