Kinds of data

The difference is where the structure lives

Structuredthe table holds the structureidnamecityjoined1AshaPune2024-03-022BenLeeds2025-01-173ChenPerth2025-06-30every row has the same typed columnsSemi-structuredeach record names its own fields{ "id": 2, "name": "Ben", "tags": ["vip"] }{ "id": 3, "name": "Chen", "phone": "+61 8 5550 1234", "address": { "city": "Perth" } }fields can differ and nest from record to recordUnstructuredno fields at allreceipt photocall recordingemailmeaning must be extracted: OCR, transcripts, AIStructured data fits a fixed schema, semi-structured data labels itself, and unstructured data has neither.Structured data lives in relational tables and is queried with SQL. Semi-structured data, JSON, XML or YAML, carries its fieldnames in every record, so records can differ; document databases and data lakes keep it. Unstructured data, images, audio,video and free text, is kept whole as files in object storage such as Azure Blob Storage or Amazon S3. Most of the world’sdata by volume is unstructured; most of the data a business runs on is structured.

Structured data fits a fixed schema, semi-structured data labels each record, unstructured data has no fields at all.

Tables

A set of rows that all share one set of typed columns

customerscustomer_idnameemailcityjoined1Ashaasha@example.comPune2024-03-022Benben@example.comLeeds2025-01-173Chenchen@example.comNULL2025-06-304Benben.k@example.comYork2025-09-12123451 column: one attribute, one typename is text; joined is a date2 header: the schema, fixed in advancea new column is a change to the table3 row: one customer, one fact eachrows have no order until a query sorts them4 primary key: unique and never emptytwo customers called Ben, two different ids5 NULL: unknown, not blank or zeroNULL = NULL is not true; test with IS NULLA table is a set of rows that all share one set of typed columns, and each row is found by its key.Codd’s relational model (1970) calls the table a relation, the row a tuple and the column an attribute. Because the columns aredeclared up front, the database can check every value as it arrives and plan every query. The primary key is what lets onetable point at a row in another, which is the whole idea of the next page.

A table fixes its columns and types in advance; each row is one fact, found by its primary key. NULL means unknown.

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.

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

Candidateany column set that could be the primary key
Alternatea candidate key that was not chosen, kept UNIQUE
Foreigna column that holds another table’s key
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.

Keys

The database refuses a change that would break a key

customersidname1Ben2Asha3Chenordersordercustomer_id101110221031The database checks every change against the keysINSERT INTO orders VALUES (107, 9);✗rejected: no customer 9violates foreign keyINSERT INTO customers VALUES (2, 'Dev');✗rejected: id 2 existsduplicate primary keyDELETE FROM customers WHERE id = 1;✗rejected: Ben has ordersunless ON DELETE CASCADEINSERT INTO orders VALUES (107, 3);✓accepted: Chen existsA primary key names each row once; a foreign key must name a row that exists, or the change is refused.This guarantee is called referential integrity: no order can point at a customer who is not there. The rule lives in the database,not in each application, so every program that writes to it obeys the same rule. ON DELETE CASCADE or SET NULL say whatshould happen to the orders when a customer is deleted.

A foreign key must name a row that exists. This is referential integrity, enforced by the database for every program.

ON DELETE actions · Going deeper

ON DELETE decides what happens to the rows that pointed at it

DELETE FROM customers WHERE id = 1; -- Ben, who has orders 101 and 103NO ACTION (the default)customer_id INT REFERENCES customersERROR: update or delete on table"customers" violates foreign keyconstraint on table "orders"DETAIL: Key (id)=(1) is stillreferenced from table "orders".nothing is deletedON DELETE CASCADEREFERENCES … ON DELETE CASCADEorder_idcustomer_id1022DELETE 1: Ben is gone, and so areorders 101 and 103, with no messageON DELETE SET NULLREFERENCES … ON DELETE SET NULLorder_idcustomer_id101NULL1022103NULLthe orders stay, with no owner recordedAlso: SET DEFAULT. ON UPDATE takes the same actions when a key value changes. RESTRICT is NO ACTION that cannot be deferred to COMMIT.The default refuses to orphan a row; CASCADE and SET NULL tell the database to tidy up instead.Use CASCADE where the child cannot exist alone (the lines of an order); SET NULL for an optional link; the default wherevera silent delete would lose records you must keep. PostgreSQL does not index a foreign key column for you: without one, everydelete of a parent scans the child. Snowflake, Databricks, Redshift and BigQuery accept PRIMARY KEY and FOREIGN KEY but donot enforce them: there they only inform the optimiser.

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 onecustomersone row each11loyalty_cardscustomer_id UNIQUErare: often just more columnssplit off for size or accessOne to manycustomersone customer1manyorderscustomer_id FKthe foreign key goes onthe many sideMany to manyordersan order has manyproductsa product is in manyorder_items(order_id, product_id) keyquantity, unit_price1nn1One-to-many is a foreign key on the many side; many-to-many needs a table of its own in between.An order holds several products and a product appears in many orders. No single column can say that, so a junction table,order_items, holds one row per pairing, with a composite key and the facts that belong to the pairing: how many, and the pricepaid that day, which may differ from today’s price. A diagram of these tables and lines is an entity-relationship diagram (ERD).

One-to-many puts the foreign key on the many side. Many-to-many needs a junction table with a composite key.

Types and constraints

Rules declared once stop bad rows at the door

CREATE TABLE orders ( order_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers, placed_at TIMESTAMPTZ NOT NULL DEFAULT now(), total DECIMAL(10,2) NOT NULL CHECK (total >= 0), status VARCHAR(10) NOT NULL DEFAULT 'new' CHECK (status IN ('new','paid','sent')));What each rule refuses✗customer_id = NULLNOT NULL✗total = -5.00CHECK (total >= 0)✗status = 'lost'CHECK (status IN ...)✗placed_at = 'next Tuesday'not a timestampFLOAT: 0.1 + 0.2 = 0.30000000000000004DECIMAL: 0.10 + 0.20 = 0.30Types and constraints stop bad rows at the door, so every reader can trust what is already inside.Store money as DECIMAL (or integer cents), never FLOAT: binary floating point cannot hold 0.1 exactly. Store moments as a timestampwith time zone (TIMESTAMPTZ in PostgreSQL, datetimeoffset in SQL Server), saved in UTC. A constraint that fails rejects the wholestatement, and the application gets an error it can show, instead of a bad row someone finds a year later.

Constraints such as NOT NULL and CHECK reject bad values. Store money as DECIMAL, never FLOAT.

Data types · Going deeper

Each type has a size, a range and a way of going wrong

TypeSizeHoldsWatch forSMALLINT · INTEGER · BIGINT2 · 4 · 8 byteswhole numbers to ±32,767 · ±2.1 billion · ±9.2×10¹⁸2147483647 + 1 → ERROR: integer out of rangeDECIMAL(p,s) = NUMERICvariesexact: p digits in all, s of them after the pointDECIMAL(10,2) tops out at 99,999,999.99REAL · DOUBLE PRECISION4 · 8 bytesapproximate: at least 6 · 15 decimal digits0.1 + 0.2 = 0.30000000000000004CHAR(n) · VARCHAR(n) · TEXTby lengthtext; n is a limit in charactersVARCHAR(3) refuses 'abcd': value too longDATE4 bytesa calendar day: no time, no zoneright for a birthday or an invoice dateTIMESTAMP · TIMESTAMPTZ8 bytesa moment, to the microsecondwith no zone it is only a wall-clock readingBOOLEAN1 bytetrue, false or NULLthree states, not two, unless NOT NULLUUID16 bytesa 128-bit id any machine can generaterandom values scatter inserts over an indexSizes and messages as PostgreSQL 16 reports them (pg_column_size).A type is the first constraint: what a column can hold, how big it is, whether its arithmetic is exact.Names differ by engine. SQL Server: BIT for a boolean, DATETIME2 and DATETIMEOFFSET, UNIQUEIDENTIFIER. Snowflake: everyinteger type is NUMBER(38,0); timestamps are TIMESTAMP_NTZ (the default), _LTZ or _TZ; VARIANT holds JSON. Spark and Delta:STRING, DECIMAL up to 38 digits, TIMESTAMP (an instant) and TIMESTAMP_NTZ. Give a key that may pass 2.1 billion rows BIGINTfrom the start: widening it later rewrites the table.

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.

Open in a new tab