Relational Database

When data lived in files, the same fact got written down in many places. Every line of an order log carried the customer company's name and address alongside it. Once that company has 8,000 orders, the same fact exists in 8,000 copies.

The trouble surfaced when the fact changed. A company renames itself and all 8,000 lines have to be edited, which in practice means some of them get edited. Now the same company exists under two names, and invoices go out carrying the old one.

There was a cost on the program side too. How to read the file, in what order, and where to split each line was written separately into every program, so changing the file format once meant finding and editing all seven programs that read it.

The relational model, proposed in 1970, addressed both at once. Data goes into tables; a fact is written into exactly one table, and other tables hold only a value pointing at that table's row. The company name lives once in the company table, and the order table holds only a company number. Reading was standardized onto one language as well, which meant programs no longer had to know anything about file layout.

It prevents the same fact from being recorded in several places, and keeps data from being left in a half-finished state.

1. It removes duplication. Keep the company name once in a company table and have the order table point at it by number, and a rename means editing one line. Splitting tables this way is called normalization, and it is the reason an address correction never has to be applied 8,000 times.
2. It blocks values that do not line up. Constrain the order table's company-number column against the company table and an order for a nonexistent company cannot be created. Each column also declares what kind of value it accepts, so stray text never lands in a signup-date field.
3. It prevents half-applied operations. Wrap deducting a balance and creating an order into one unit, and a failure partway through leaves neither of them having happened. That unit is a transaction, and the four properties it has to honor are named ACID.
4. You state what you want and the database decides how to find it. Write a request in SQL for members who signed up last month without a payment, and which table to read first and in what order to match them is the database's choice. That is why the same statement keeps working when the data grows a hundredfold.
5. It extracts speed from the same data. Put an index on a frequently searched column and a query that scanned a million rows reads a handful instead. It works like the index at the back of a book.

Where declaring a table shape up front is a burden, other families get chosen. What separates them is the shape the data is held in.

1. NoSQL is the family that does not require the shape in advance. Records can carry different fields from each other, and adding a field does not mean altering a structure. That suits data whose shape changes often, or an early stage. In exchange, joining records together becomes the program's job.
2. A key-value store hangs one value on one key and retrieves only by that key. Its simplicity makes it fast, which suits holding session state or a frequently recomputed result for a short while.
3. A vector database stores the meaning of a sentence as a list of numbers and finds what is close in meaning rather than in characters. Search that groups "how do I get a refund" with "I want my money back" comes from here.
4. A data warehouse is a separate store assembled for analysis. Running large aggregate queries against the database serving the product slows down user-facing screens, so the data gets copied out on a schedule and the heavy queries run against the copy.

The usual outcome is not picking one of the four but using several. Core data like accounts and orders sits in a relational store, session state in a key-value store, and analysis in a warehouse.

"This one goes relational. Accounts and payments must never disagree."

A judgment that this data cannot tolerate an operation landing halfway, which means the store has to enforce it rather than the code.

"Split the tables and a company rename is a one-line edit."

Proposing the design that removes duplication, so the fact lives in exactly one place.

"This query takes three seconds. I think it needs an index."

Speed has to come out of the same data rather than out of more hardware.

At Pagedeck, Theo put accounts, teams and document permissions in a relational store. A document carries several permissions and each one has to be exactly right about who received it, and with the tables constrained against each other, a permission attached to a nonexistent account cannot be created.

Document bodies live somewhere else. Edit history keeps accumulating and the shape differs per document, which makes tables an awkward fit. The screen Mia uses for weekly usage is drawn from data copied into the warehouse. There was an earlier period when those aggregates ran directly against the service database, and the document list slowed down every Monday morning.

The most common error is treating SQL and a relational database as the same thing. The two words travel together, so the assumption is easy. But one is a way of holding data and the other is a language for addressing it. There are many relational products with slightly different dialects, and products that do not use tables at all also offer SQL-like languages. Collapse the two and a meeting about which store to choose turns into a discussion about syntax.

The second error is assuming NoSQL is better because it is newer. The name sounds like something that arrived after relational, and claims about speed travel with it. But that speed is usually the price of enforcing less. Without declared column types, six months later the same field is stored under three different names. Without constraints between tables, the program has to filter out permissions pointing at accounts that no longer exist. It is not a question of new against old but of deciding what the database enforces and what the program carries.