Code Smoothie

Database Normalization Simplified

An Important Aspect of Database Best Practice Explained In Simple Terms. Applies to SQL, MongoDB, and pretty much any other database.

Common Scenario

Say you want to keep track of Students and their courses. How would you go about doing this?

You might have do it like this, with a single table

Name Birthdate Gender Courses
Bob Doe 1/1/2000 Male English II, Calc AB

This might be appealing due to it’s simplicity, but things could get complicated. Some problems I can think of off the top of my head are:

You might add another table courses to help with the validation.

Name Start Stop
English II 8/20/2015 6/5/2016

Only some of the problems are fixed. You still have the problem of dealing with renamed courses and more.

A better solution

Use another table to keep track of relationships between two things.

We’ll call this table enrollment. It keeps track of course-to-student relationships.

Course_Name Student_Name
English II Bob Doe
Calc AB Bob Doe

There are still a few more problems. We need a way to link things together without making references that may change or have duplicates.

Use Foreign Keys!

It’s really simple. Reference the ID of the entry instead of its English name. (And add an id field to the object if there already isn’t one.)

enrollment:

course_id student_id
1 1
2 1

students:

ID Name Birthdate Gender
1 Bob Doe 1/1/2000 Male

courses:

ID Name
1 English II
2 Calc AB

The concept is to reduce redundancy. This leads to maintainability and only having to update the value in one place.

There are even more benefits to this approach. You can further describe the relationship by adding fields such as date_enrolled.

Closing and TL;DR

Use another table to map one to many relationships. Don’t be afraid to use another table.

Use foreign keys - references using a unique ID, rather than an English Name.

Because this could also result in writing lots of annoying queries, you might also like my article about how I implement this in Meteor.