Table

Back when data lived in files, whoever created the file decided its shape. Every department kept its own member list — one recorded names and phone numbers, another added the channel each person signed up through.

The trouble appeared when those files had to be combined. Stack two of them and the same person occupies two rows, one with a phone number and one without, and nothing says which row is right. When the person who maintained a file left, nobody could say what its second column meant. Values accumulated, but none of it was usable data, and every attempt to answer a question about them turned into an argument about which file to believe.

A table fixes the shape first and pours values into it. The columns are decided in advance and only rows get added, so the same kind of value lands in the same place no matter who entered it. Combining becomes appending, and the shape doubles as documentation. Adding a hundred more rows changes nothing about how the data is read, because the reading always follows the columns. Someone new can scan the column names and know what the table holds, instead of depending on one person's memory.

Columns are settled up front and rows stack up underneath. A column is one field and a row is one subject with those fields filled in. In a members table, name, email and signup date are the columns, and one member is one row.

Working with a table takes four moves.

1. Define the columns. You decide what each one holds and what kind of value it accepts — name as text, signup date as a date, view count as an integer. Collect those definitions in one place and you have a schema.
2. Insert rows. Every registration adds one. The columns stay as they are while the rows grow to 2 million, which is why a table handles growth that a hand-built file never could.
3. Address a single row. Names won't do it, since two members can share one, so a separate non-repeating number is kept for the purpose. That column is the primary key.
4. Connect tables. Store an author number on each row of the documents table and point that column at the members table's primary key. The pointing column is a foreign key.

There is a reason data gets split across tables instead of crammed into one. If a member writes 300 documents, the members table has nowhere to put them. Fitting them in would mean 300 columns named document1, document2, and the 301st document breaks it again. So documents move into their own table and connect back by author number. Every one-to-many relationship gets solved this way. More tables is not a loss: the narrower each table's job, the simpler the queries that read it.

Splitting further so that no fact is written twice is normalization; deliberately leaving duplication in for speed is denormalization. How many tables a feature ends up with is decided between those two.

The word comes up in three situations.

1. Reading — pulling data with SQL starts by naming the table. A single dashboard number is often the result of matching three or four tables, so when the number looks wrong, the first step is retracing which tables were matched and which of two same-named columns supplied the value.
2. Designing — every new feature raises the question of whether to add a table or add a column to an existing one. For a favorites feature, the options are a column on the documents table or a new favorites table. The test is whether the value is one per subject or many.
3. Performance — once a table grows, read speed becomes the issue. An index is a pre-sorted structure for finding particular values fast, and which column of which table to put it on is the discussion at that point.

With an object-relational mapper (ORM), tables appear in code as objects — one row of the documents table becomes one document object. It's convenient, and it also hides how many tables a single operation touches, so when a page is slow you have to check what was actually read and from where.

"Which table is that value in?"

Asking whether the data is even stored, and if so where to read it from.

"Couldn't we just add one more column to the members table?"

Floating the cheapest-looking option, which usually leads to a question about whether that value is one per member or several.

"I think this needs its own table."

There's no room for it in the current structure, so a new table has to be created.

That exact exchange happened when Pagedeck added document favorites. Mia assumed a favorites column on the documents table would do it, and Theo pointed out that such a column can't say who favorited the document. A favorite isn't a property of a document; it's a relationship between a member and a document, and a relationship between two things needs a place of its own to live.

The answer was a separate table holding a member number and a document number on each row. Someone who favorites 50 documents produces 50 rows, and unfavoriting deletes one. Shaped that way, "documents I favorited" and "people who favorited this document" both come out of the same table.

It's easy to treat a table as a spreadsheet. The grid of rows and columns really does look alike. The difference is where the rules live. A spreadsheet accepts text typed into a date cell; a table refuses it. A spreadsheet loses one person's edit when another saves over it; a table takes writes from hundreds of people at once and keeps every row. Design with spreadsheet instincts and you add columns freely and mix value types, and a few months later nothing counted from that table can be trusted, because half the rows follow one convention and half follow another.

The other common belief is that adding a column is free. It's one line in the schema and one input on a screen, so it looks small. But you owe an answer for what fills that column in the 2 million rows already stored, and every piece of code and every screen that reads it comes along too. Add a column for something that can have several values per member and a much larger job — splitting it into its own table — is waiting later.