How Apps Remember You
Your high scores, your messages, your friends list. Where does all of it actually live?
Your high scores, your messages, your friends list. Where does all of it actually live?
Imagine a pet adoption app that dumps everything into one big list: each pet and its owner, over and over. It works at first. Then Maria adopts three pets, and her email gets copied into three rows. Fix a typo in one, and the others are still wrong.
Copied data is the enemy. When the same fact lives in many places, it drifts out of sync. Databases exist to store each fact exactly once.
The fix: make one table per kind of thing. Owners go in an owners table. Pets go in a pets table. Now Maria's email is stored once, in one place. Press organize and watch the mess sort itself out.
There is still a problem: two owners could both be named "Maria." So each owner gets a unique id — its key. A pet then stores its owner's id instead of the name. Click a pet, then click its owner, to draw the link.
A key in one table stored inside another table is a relationship. "This pet belongs to that owner." It is the single most important idea in database design.
Swap "pets and owners" for anything, and you have the app. The same two-tables-and-a-key idea powers the whole internet. Tap each one.
Players and their high scores. Each score row points back to the player who earned it.
Users and messages. Every message stores which user sent it and which chat it belongs to.
Users, posts, and follows. A "follow" is just a row linking one user to another user.
Books and borrowers. A loan row connects one book to the person who checked it out.
The language apps use to ask these tables questions is called SQL, invented in the 1970s. "Show me Maria's pets" becomes one short line of SQL — and Database Designer writes it for you.
Every app you love remembers people, scores, and messages the same way: as rows in tidy tables that link together. Deciding what those tables are and how they connect is called database design. Now go design one.
A table is a spreadsheet for one kind of thing. Every row is one record (one user, one pet), and every column is a field (a name, an email).
A unique id tells records apart, even when two people share the same name. That id is called the key.
Instead of copying data everywhere, one table points to another using its key. A post belongs to a user; a pet belongs to an owner.
Games, chats, and social apps all store you as rows in connected tables. Designing those tables well is what keeps an app fast and tidy.
Put your new knowledge into practice!