Skip to Main Content

Write Great SQL Office Hours

Free tips and training every month! Subscribe for reminders and more from Office Hours. FAQ

Header container

August 17

13:00 UTC   Start Times Around the World

Subscribe to be notified of changes to sessions and give us feedback!

Having trouble watching the video on this page? Open the video in your browser.

Description

How to normalize your tables to improve data quality – back to basics
The relational model is built on the principles of data normalization. This improves data quality by avoiding duplication and preventing data errors.

This video shows the problems that can occur when you store unnormalized data. It then walks through normal forms 1, 2, and 3. For each Chris discusses their criteria, how to fix your model to meet each form, and how this avoids further problems.

Highlights from this session include:

1:10 - Errors that can occur in unnormalized data
3:00 - Overview of normal forms 1-3
4:30 - First normal form
6:10 - The problems with storing comma-separated lists
11:00 - Converting comma-separated lists to columns
14:30 - Avoiding duplicate rows with primary keys/unique constraints
16:50 - Surrogate keys
17:30 - Candidate keys
19:30 - Converting comma-separated lists - rows or columns?
32:20 - Functional dependencies
34:10 - Second normal form
36:20 - Foreign keys
43:45 - Third normal form
47:50 - Derived values - storing vs calculating at runtime
51:30 - Using virtual columns to store formulas
52:50 - Check constraints to keep stored derived values in sync
55:15 - Quiz: is this design in 3NF?
59:50 - Recap
1:01:50 - Further reading

Your Experts

Chris Saxon
Chris Saxon, Oracle Developer Advocate for SQL    
Chris Saxon is an Oracle Developer Advocate and quizmaster on Dev Gym. His job is to help you get the best out of the Oracle Database and have fun with SQL! He's also part of the answer team on Ask TOM and creates YouTube videos at The Magic of SQL channel.