Kinds of data
The difference is where the structure lives
Structured data fits a fixed schema, semi-structured data labels each record, unstructured data has no fields at all.
All data has some shape, and the first question is where that shape is kept. Structured data lives in a table. The table declares its columns once, and every row fills the same ones. Semi-structured data, like JSON, writes its field names into every record. One customer has tags, the next has a phone and a nested address. Nothing forces them to match. Unstructured data has no fields at all: a photo of a receipt, a call recording, the body of an email. Before you can ask it a question, something has to extract the meaning.
Tables
A set of rows that all share one set of typed columns
A table fixes its columns and types in advance; each row is one fact, found by its primary key. NULL means unknown.
The relational model rests on one structure, the table. Each column is one attribute with one type. The header is the schema, and it is fixed before any data arrives. Each row is one customer. Rows have no built-in order. The primary key, customer id, is unique and never empty, so two customers named Ben are still two different rows. And a NULL city does not mean blank. It means unknown, which is why NULL is never equal to anything, not even another NULL.
Tables · Going deeper
Natural, surrogate and composite keys
A surrogate key is a number with no meaning; a natural key is a real attribute; a composite key uses two or more columns together.
A surrogate key is a number with no meaning; a natural key is a real attribute; a composite key uses two or more columns together. Three kinds of key. Natural key a value that already identifies the thing: an ISBN, a country code, an email address. Readable, but it can change (people change email) and it is often long. Surrogate key a number or UUID the database makes up: customer_id 42. It never changes and carries no meaning, which is why most tables use one. Keep the natural key too, with a UNIQUE constraint. Composite key two or more columns that are unique only together: (order_id, product_id) in a table of order lines. Common in junction tables. Other words for keys. Generating keys: an identity column (GENERATED ALWAYS AS IDENTITY in PostgreSQL, IDENTITY in SQL Server) counts up 1, 2, 3. A UUID can be made anywhere without asking the database, which suits distributed systems, at 16 bytes instead of 4 or 8.
Three kinds of key
- Natural key a value that already identifies the thing: an ISBN, a country code, an email address. Readable, but it can change (people change email) and it is often long.
- Surrogate key a number or UUID the database makes up: customer_id 42. It never changes and carries no meaning, which is why most tables use one. Keep the natural key too, with a UNIQUE constraint.
- Composite key two or more columns that are unique only together: (order_id, product_id) in a table of order lines. Common in junction tables.
Other words for keys
Keys
The database refuses a change that would break a key
A foreign key must name a row that exists. This is referential integrity, enforced by the database for every program.
Keys are not just labels. The database enforces them. Insert an order for customer nine, who does not exist, and it is rejected. Add a second customer with id two, and it is rejected: a primary key is unique. Delete Ben while orders still point at him, and that is rejected too, unless you said to cascade the delete to his orders. An order for Chen, who exists, goes straight in. Because these rules live in the database, every application that writes to it obeys them.
ON DELETE actions · Going deeper
ON DELETE decides what happens to the rows that pointed at it
By default a parent with children cannot be deleted. ON DELETE CASCADE deletes the children too; SET NULL unlinks them.
Relationships
Many-to-many needs a table of its own in between
One-to-many puts the foreign key on the many side. Many-to-many needs a junction table with a composite key.
Tables relate in three ways. One to one: each customer has at most one loyalty card. It is rare, because the card could simply be more columns. One to many is the common case: one customer, many orders. The foreign key always goes on the many side, in orders. Many to many: an order has many products and a product is in many orders. No single column can hold that. It takes a third table, order items, with one row for each pairing, keyed on both ids, and carrying the facts that belong to the pairing: the quantity, and the price paid that day.
Types and constraints
Rules declared once stop bad rows at the door
Constraints such as NOT NULL and CHECK reject bad values. Store money as DECIMAL, never FLOAT.
A table definition is also a list of rules. NOT NULL refuses an order with no customer. The CHECK refuses a negative total. Another CHECK limits status to three known words. And the type itself refuses a placed-at value that is not a timestamp. Types matter for numbers too. A FLOAT is binary, and cannot hold one tenth exactly: point one plus point two comes out as 0.30000000000000004. For money, use DECIMAL, which stores exact digits.
Data types · Going deeper
Each type has a size, a range and a way of going wrong
INTEGER holds up to 2.1 billion in 4 bytes; DECIMAL is exact; a timestamp with time zone is an unambiguous moment.
Lesson complete
You finished “Data and tables”
Nicely done.