Tables, Keys and Relationships

Why a database rather than a spreadsheet

• Concurrency — many users reading and writing simultaneously without overwriting each other.
• Integrity — rules enforced by the system, so invalid data cannot be entered.
• Scale — millions of rows queried efficiently.
• Security — access controlled per user, per table, and per column.
• Recoverability — transactions, backups and point-in-time recovery.
• A query language — ask a new question without rebuilding anything.

The structure

• A table holds one kind of thing: customers, orders, assets, readings.
• A row is one instance of that thing.
• A column is one attribute, with a defined data type.
• A schema is the collection of tables and the rules connecting them.

Data types

Choosing correctly prevents a whole class of problems:

• Integer for whole numbers; decimal or numeric for money, never floating point — floating point introduces rounding errors that accumulate and produce accounts that do not balance.
• Character types — variable length for most text, with a sensible maximum.
• Date, time and timestamp — always store as a date type, never as text. Store timestamps in UTC and convert for display; mixing local times across regions is a persistent source of error.
• Boolean for true and false.
• JSON for genuinely variable structure — useful, and often a sign that something should have been a proper table.

Keys

• Primary key — uniquely identifies a row. Every table should have one. Prefer a meaningless surrogate key (an auto-generated number or UUID) over natural data, because natural identifiers change — email addresses, registration numbers and even national identifiers get corrected.
• Foreign key — a column referencing the primary key of another table. This is what enforces referential integrity: you cannot create an order for a customer who does not exist, and you cannot delete a customer who has orders without deciding what happens to them.
• Unique constraint — no duplicates in a column that is not the primary key.

Relationships

• One-to-many — one customer, many orders. The foreign key lives on the many side. The most common relationship by far.
• Many-to-many — courses and learners. Requires a third junction table holding the pairs, which is also the natural place for attributes of the relationship such as enrolment date and result.
• One-to-one — usually an indication that the two tables should be one, unless the split exists for security or optional data.

Constraints

NOT NULL, UNIQUE, CHECK for value rules, and DEFAULT. Enforce rules in the database, not only in the application: the database is the last line of defence, applications change, and data arrives through imports and scripts that bypass application logic entirely.