A humble guide to database schema design - Mike Alche

www.mikealche.com
7 min read
fairly easy
Having a good database schema design is crucial for building applications that will need to scale in traffic and complexity. If you happen to do a bad design choice you will see that it takes a lot of effort to stop the pattern from propagating to your backends' services and controllers, and finally to your …
Having a good database schema design is crucial for building applications that will need to scale in traffic and complexity. If you happen to do a bad design choice you will see that it takes a lot of effort to stop the pattern from propagating to your backends' services and controllers, and finally to your frontend.

So there is a need to be able to evaluate if a database design is better than another.

But what does good mean when we are talking about design?

Well, there are at least two things that we want to be sure to do:

Preserve everything we know when we split our information into tables Reduce the redundancy of how we store it

Regarding the second point: do we only want to reduce redundancy because of a storage size problem? No, we do it mainly because having redundant data leads you to inconsistency problems if you don't update all the fields that represent the same information when an update occurs

Here I present some guidelines that I believe will help achieve a good design:

Use at least third normal form Create a last line of defense with constraints Never store full addresses in a single field Never store firstname and lastname in the same field Establish conventions for table and field names, and keep them

1) Use at least third normal form

The design of databases can be separated into these categories:

First normal form

Second normal form

Third normal form

Boyce-Codd normal form

The idea is that these categories act like a proxy to classify how good is the design

I'll quickly explain each one so that you can understand why we want to be in third normal form.

First normal form

For a database to be in first normal form then every value of every column of every table should be atomic

What does atomic mean?

Speaking lightly atomic means that the value represents a "single thing"

For example, if you have a table like this

first_name last_name age areas John Doe 27 {"Website design", "Customer research"} Mary Jane 33 {"Long term…
Read full article