SQL Database Design Practices

This training covers SQL database design best practices for developers and data professionals, teaching how to structure efficient, scalable, and maintainable databases.

A digital instructor presents all 14 pages. Hold “Ask” at any point and ask out loud — the answer comes from this course. No sign-up needed.

28 minFree to watchDownloads

What you’ll learn

01Introduction to SQL Database Design PracticesWelcome. I'm glad you're here. This course is about SQL database design practices, and our goal is simple: help you build relational schemas that stay correct and stay fast as your application grows. This is for developers, junior database designers, data engineers, analysts, and students. Over the next slides, we'll walk through requirements, entity relationship modeling, normalization, keys, indexing, and migrations. You'll also pick up core vocabulary like schema, relation, tuple, attribute, and functional dependency. And you'll see a workflow that moves from requirements to conceptual, logical, and physical design, then validation and evolution. Why does this matter? Because schema quality drives correctness, query speed, and long-term maintainability. A weak schema shows up later as inconsistent data and slow queries. So here's the 2026 default we'll return to. Normalize first, enforce integrity in the database, and plan migrations early. Treat these as choices with tradeoffs, not absolute rules. Next, we'll look at the course roadmap and the design lifecycle.querydeck.app jusdb.com exasol.com+21 min
02Course Roadmap and Design LifecycleLet's walk through the roadmap we'll follow, and the design lifecycle behind it. Think of schema design as four stages: requirements, conceptual, logical, and physical. They run in order, but with feedback loops, so you can circle back when something doesn't hold up. Here's why that matters. Skip a stage, and the fragility compounds. Design debt grows with every release, and it gets harder to untangle. Notice that abstraction narrows as you move: conceptual ER models stay broad, while physical choices get engine-specific, like index types or partitioning. Different roles own different stages. Stakeholders drive requirements, data architects shape the conceptual model, developers handle logical design, and DBAs own physical implementation. A useful instinct is to model technology-agnostic first, and make physical decisions last, driven by your target engine. Before deployment, validate with representative data and real queries. That step is non-negotiable, because a schema that looks right on a whiteboard can still fail under real load. So keep the four stages in mind, expect to revisit them, and let evidence guide your choices. Next, we'll dig into Requirements Analysis and Conceptual Modeling.querydeck.app jusdb.com exasol.com+22 min
03Requirements Analysis and Conceptual ModelingLet's move into requirements analysis and conceptual modeling. This is where you turn business requirements into a picture of the data before you commit to any tables. Start with an entity relationship diagram, or E R D, before writing any D D L. The reason is simple. Fixing a relationship on a whiteboard takes minutes. Fixing it in a live migration takes days and risks real data. As you model, capture cardinality clearly. One to one, one to many, and many to many, along with participation and optionality. For example, an order must belong to exactly one customer, but a customer may have zero or many orders. When you find a many to many relationship, resolve it with a junction table rather than a list or array column. A junction table keeps your schema flexible and easy to query. You also need to decide what is genuinely an entity, what is just an attribute, and what is better described as a relationship. Then validate the whole model with stakeholders using real business questions. If it answers those questions, your model works. From Conceptual Model to Logical Schema.erstudio.com arxiv.org upgrad.com+22 min
04From Conceptual Model to Logical SchemaNow let's move from the conceptual model into the logical schema. This is where your entities and relationships start turning into actual database structures, while still staying independent of any specific database product. The first step is straightforward: each entity becomes a table, and each attribute becomes a column. Next, you assign primary keys and migrate foreign keys across your relationships. If a customer places orders, the order table carries a foreign key back to the customer. At this stage, keep the model technology-agnostic. No data types, no vendor-specific features yet. That decision comes later, and staying neutral here keeps your options open. Many-to-many relationships need special care. You resolve them with an associative table, not a list column. An order and a product, for example, become a third table holding both keys plus any relationship attributes like quantity. Subtypes and optional relationships should be modeled explicitly rather than hidden behind nullable catch-all columns. It takes more thought upfront, but it prevents confusion later. Follow these steps in a disciplined order. That discipline is what prevents semantic drift from the original conceptual model. Next, we'll look at normalization fundamentals, from first normal form through B C N F.erstudio.com arxiv.org upgrad.com+21 min
05Normalization Fundamentals: 1NF through BCNFLet's move on to normalization fundamentals, specifically first normal form through Boyce-Codd normal form. Every normal form is defined by functional dependencies and candidate keys, so those two ideas are your foundation. A functional dependency simply means one attribute determines another. A candidate key is a minimal set of columns that uniquely identifies a row. Now, first normal form says each column holds one atomic value. No lists, no comma-separated strings, no repeating groups. Why does that matter? If a cell holds multiple products, you cannot reliably update or query just one of them. Second normal form applies mainly when your primary key is composite. Every non-key column must depend on the whole key, not just part of it. Otherwise you get redundancy. For example, if an order line table stores product name alongside order id and product id, that name depends only on product id, so it belongs in its own table. Third normal form removes transitive dependencies, where a non-key column depends on another non-key column. This is the practical target for most transactional systems. Boyce-Codd normal form is stricter. Every determinant must be a superkey, which catches rare cases where overlapping candidate keys still cause anomalies. Here's a key takeaway. Each form removes one class of insert, update, or delete anomaly. You do not need to memorize them as abstract rules. Just ask what depends on what, and split accordingly. Next, we will walk through a worked example and look at when denormalization makes sense.cs.uct.ac.za opencs.aalto.fi digitalocean.com+22 min
06Practical Normalization: Worked Example and Denormalization Trade-offsLet's walk through a practical example. Imagine one flat order table where the product column holds a list, and customer details repeat on every row. That single table carries partial and transitive dependencies, which is exactly what normalization untangles. First normal form makes each value atomic, so you split those product lists into separate rows. Second normal form removes dependencies on only part of a composite key, like quantity depending on order and product together, not just the order. Third normal form then extracts customers and warehouses, so no non-key column determines another. BCNF goes one step further, splitting a table whenever a non-key column determines another column. Here is the important part. Normal forms check structure, not design. A schema missing a whole table can still pass every rule. So treat normalization as your starting point, not your finish line. Denormalize only for measured reads, one column at a time, with synchronization and monitoring. And often, caching layers or materialized views remove the need entirely. Next, we will look at keys, constraints, and data integrity.cs.uct.ac.za opencs.aalto.fi digitalocean.com+22 min
07Keys, Constraints, and Data IntegrityNow let's talk about keys, constraints, and data integrity. These are the rules that keep your data trustworthy. For identity columns, default to BIGSERIAL. It is compact, fast, and readable. Reach for UUID version seven only when IDs must be unique across databases, like in sharded or offline-first systems. Prefer surrogate keys over natural keys, and never expose sensitive values like a Social Security number or an email address as a primary key. Foreign key actions matter. CASCADE is for dependent rows like order line items. RESTRICT protects important entities. SET NULL fits optional relationships where the child can stand alone. Then enforce your rules in the database with NOT NULL, UNIQUE, CHECK, and DEFAULT constraints. Frontends change, and scripts bypass application logic, so the database should be your gatekeeper. Finally, consider soft deletes using a deleted_at column, paired with partial unique indexes and audit columns. That preserves history without breaking uniqueness for live rows. Next, we will look at physical design, covering data types and storage decisions.learn.microsoft.com dev.mysql.com dev.mysql.com+21 min
08Physical Design: Data Types and Storage DecisionsNow let's move into physical design, where those logical decisions turn into actual data types and storage choices. Start with money. Never use FLOAT or DOUBLE for monetary values, because floating point rounding will quietly corrupt your totals. Use NUMERIC with a precision of nineteen and a scale of four, or store integer cents. For your primary key, BIGSERIAL is eight bytes, while a UUID is sixteen, and benchmarks show BIGSERIAL reads can be twenty to forty times faster. Reach for UUIDs only when IDs must be unique across databases without coordination. Use TIMESTAMPTZ rather than TIMESTAMP, and always add created_at and updated_at columns. They cost almost nothing and they are invaluable when you are debugging. For email, VARCHAR two hundred fifty four with a CHECK constraint works well, and use TEXT when length is not a real business rule. Keep JSONB for genuinely semi-structured data, with CHECK constraints and documented scope. Finally, prefer BIGINT over INT for any growing identifier. The four byte saving is not worth a painful migration later. Next, we will look at indexing strategy, including B-tree, composite, covering, and partial indexes.querydeck.app jusdb.com exasol.com+22 min
09Indexing Strategy: B-tree, Composite, Covering, and Partial IndexesLet's talk about indexing strategy. Think of an index as a lookup structure, and each type is built for specific query operators. B-tree is your default for equality, range, and sort on scalar values. For JSONB, arrays, and full-text search, use GIN instead, because those queries check containment, not simple comparisons. Now for composite indexes, column order really matters. Put equality columns first, then range or sort columns, so the planner can narrow the scan efficiently. Covering indexes with INCLUDE add payload columns that enable an index-only scan, avoiding heap lookups for read-heavy queries. Just keep the included columns small, or the index grows fast. Partial indexes shrink size on status and soft-delete flags by indexing only the rows your queries actually target. But here's the tradeoff: every index slows writes. On a typical OLTP table, aim for roughly six indexes, then review for unused ones. Next, we'll see how to validate these decisions with query plans.1 min
10Validating Design Decisions with Query PlansNow let's validate your design decisions by reading query plans. Start with EXPLAIN ANALYZE and BUFFERS. Four things matter most: Index Cond, Filter, Sort, and Heap Fetches. If your predicates show up under Filter instead of Index Cond, that usually means your composite index column order is wrong. The planner cannot seek, so it scans and checks afterward. Next, know your scan types. Index Scan fetches rows one by one for selective predicates. Bitmap Heap Scan batches scattered rows. Index Only Scan reads entirely from the index when Heap Fetches is zero. Also, when a column is low cardinality and evenly distributed, the planner ignores the index. That is correct, because random reads would cost more than a sequential scan. So write your top queries first, then design indexes from those access patterns. And after bulk loads, run VACUUM ANALYZE to refresh statistics and the visibility map. Next, we move into schema evolution with migrations, version control, and the expand contract pattern.2 min
11Schema Evolution: Migrations, Version Control, and Expand-ContractNext, let's talk about schema evolution. Migrations, version control, and the expand-contract pattern. Treat numbered, version-controlled migration files as the source of truth. That way, the schema a database has is always traceable to a known file, not to someone's memory. Keep changes backward-compatible by default. Add new columns as nullable first, then apply constraints later, once you are sure the data is ready. The pattern is expand, backfill, contract. Add the new structure, dual-write while you migrate data in batches, then drop the old structure. This buys you a compatibility window, so old and new code can run side by side. For zero-downtime changes, use concurrent index creation, add constraints as not valid and validate them separately, and set a short lock timeout so a slow query cannot stall your table. Follow the N and N minus one rule. The running code and the current schema must always agree, in both directions. Finally, remember that contract is irreversible. Gate it on zero mismatches, no nulls in the new column, and a snapshot you can restore from. Everything before contract is reversible. Contract is not. With that foundation, let's look at common antipatterns and how to avoid them.2 min
12Common Antipatterns and How to Avoid ThemLet's look at some common antipatterns and how to avoid them. An antipattern is a design that looks reasonable at first but causes real pain later. The first is the EAV table, short for entity attribute value. It stores attribute names and values as rows, so you lose type enforcement, and every query turns into a pile of self joins. EAV is a hard sell. Next, polymorphic associations. That's a type column plus an id column pointing at several different tables. It looks tidy, but the database cannot enforce a foreign key across those pairs, so integrity quietly breaks. Prefer separate tables per type, or a shared parent table the children reference. Then the god table, one giant table mixing unrelated domains. When you change billing, you touch authentication. Split by domain and link the pieces with foreign keys. Also, watch for delimited lists stored in a single column. Put those in an intersection table instead. And replace clusters of boolean flags with a single state enum. Finally, keep naming consistent with snake case, and audit regularly by querying for tables with unusually high column counts and for missing foreign keys. Small habits like these keep your schema honest. Next, we'll cover security, privacy, and multi tenancy in schema design.2 min
13Security, Privacy, and Multi-Tenancy in Schema DesignLet's talk about security, privacy, and multi-tenancy in schema design. When your database serves more than one customer, isolation becomes a design decision, not an afterthought. You have three main choices, and each has tradeoffs. Shared schema with a tenant underscore i d column is cheapest to operate and scales past ten thousand tenants, but a forgotten filter can leak data, so you enforce it with Postgres row level security. Schema per tenant gives you logical isolation auditors accept, typically two hundred to two thousand tenants per cluster. Database per tenant is maximum isolation for regulated work and big contracts, but it costs far more to run. With row level security, the USING clause filters what a tenant can read, and WITH CHECK blocks cross-tenant writes. Make sure your application role is not a superuser and does not have BYPASSRLS, and force row level security on the owner. Then set app.current_tenant inside each request, and remember SET LOCAL resets at transaction end. Never trust job payloads or cached keys without tenant context. Next, we'll put this into practice in the hands-on workshop on designing a relational schema end to end.2 min
14Hands-On Workshop: Designing a Relational Schema End to EndLet's bring everything together. We'll walk one scenario end to end, from requirements to conceptual, logical, and physical design. As you go, apply normalization, keys, constraints, and indexes to that domain. Then run your candidate queries and read the execution plans, so you can confirm whether each index actually earns its place. When something looks risky, schema linters help. Tools like SlowQL, Relune, and pgcrab check for missing primary keys, foreign keys without indexes, and duplicate or overlapping indexes, and they can validate your queries against a DDL file. Generate realistic test data, run review checklists, and document tables and columns with COMMENT ON so future readers understand the intent. This is the last slide, so here is your takeaway. A good schema is designed in layers, verified with real queries, and documented. Keep practicing, thank you for joining, and good luck with your next schema.2 min

Take the deck with you

Download this course as a file — free, no sign-up needed.

PDF handoutEvery slide page, ready to print or share.15 pages · 3.4 MBDownload
Narrated PowerPointThe deck that presents itself — every slide carries the digital human's narration video.15 pages · 17.8 MBDownload
PowerPoint slidesThe full deck as a .pptx — open it in PowerPoint, Keynote, or Google Slides.15 pages · 3.3 MBDownload

Free to use in your own training — please keep the PersonWise credit page at the end.

Have your own deck? Turn it into a course

Sources consulted

Web sources consulted while building this course.