Database Normalization Simplified
An Important Aspect of Database Best Practice Explained In Simple Terms. Applies to SQL, MongoDB, and pretty much any other database.
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
|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:
- How do you validate the
- How do you know whether a course exists?
- What if a course is renamed?
- How do you find which students are enrolled in a given course?
You might add another table
courses to help with the validation.
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.
|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.)
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
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.