How do we divide up a large table into smaller tables in practice?


#1

Question

We see here that splitting a large table into multiple smaller tables can reduce redundant data and make our tables more manageable. How do we go from one massive table to multiple smaller tables?

Answer

A good first step towards dividing up a large table is to think about the pieces of key information that we’re storing in our application. Once we’ve identified these keys, for example name, email, and phone number, we ask whether or not a single value for any of the keys can be associated with multiple values for another key. For instance

Can a name be associated with multiple email addresses?
Can an email be associated with multiple phone numbers?
etc.

If we decide that a value for a key can be associated with many values for another key, we divide those keys into their own tables. We continue doing this until either

  1. Each key has its own table, or

  2. If multiple keys share a table, a value for any key is associated to exactly one value for the others.

In this way, we have successfully divided our table into smaller tables without redundancies. We call these keys the primary keys for our application.