Tables, rows, and why structure matters
A relational database organizes data into tables — rows are records, columns are the fields each record has. The relational part comes from how tables reference each other rather than duplicating data: an orders table doesn't repeat a customer's full name and address on every row, it stores a customer_id that points back to a customers table.
Primary and foreign keys
A primary key uniquely identifies a row within its own table — usually an auto-incrementing integer or a UUID. A foreign key is a column in one table that references a primary key in another, and it's how relationships get expressed:
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
total DECIMAL(10,2)
);The REFERENCES constraint means the database itself will reject an order pointing at a customer that doesn't exist — data integrity enforced at the storage layer, not left to application code to remember.
Normalization: avoiding duplicate, conflicting data
If a customer's address is stored on every one of their orders and they move, you now have to update it in every row — miss one, and your data disagrees with itself. Normalization means storing each fact in exactly one place and referencing it everywhere else. The tradeoff is that reading related data now requires combining tables back together.
Joins: combining tables back together
SELECT orders.id, customers.name, orders.total
FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE orders.total > 100;This is the operation normalization trades for: instead of one flat table, you now ask the database to stitch related rows together at query time. It's a good trade — the database is built to do this fast, and your data can never silently contradict itself.
Indexes: the difference between fast and slow at scale
Without an index, finding a row means scanning every single one — fine for a hundred rows, unusable for ten million. An index is a separate, sorted structure the database maintains alongside your table, letting it jump straight to matching rows instead of checking each one:
CREATE INDEX idx_orders_customer ON orders(customer_id);The tradeoff: every index speeds up reads but slightly slows down writes, since the index has to be updated too. Index the columns you actually filter and join on — indexing everything "just in case" mostly adds overhead without benefit.
When NOT to use a relational database
Relational databases assume your data has a fairly consistent shape and that relationships between records matter. If your data is more like independent documents with wildly varying structure — user-generated content with arbitrary fields, for instance — a document database (MongoDB and similar) can be a better fit. The honest default, though, is to start relational: most applications benefit more from the integrity guarantees than they're held back by the rigidity.
The habit that prevents the most pain
Before adding a table, ask what should be true about this data no matter what — can two rows have the same value here, does this always need to point at something that exists, should this ever be empty. Encode those answers as constraints (UNIQUE, NOT NULL, foreign keys) rather than trusting application code to enforce them consistently forever.
Comments