Tables, keys and a messy spreadsheet

Structured, semi-structured, non-structured

Structured data fits neat rows and columns where every row has the same fields, like an orders table. Semi-structured data has labels but no fixed layout, like a JSON file where a toy has a color and kibble has a bag size. Non-structured data, also called unstructured, has no data model at all: photos, videos, recorded calls, free-written emails.

{"id": "P7", "name": "Rope toy",
 "color": "red"}
{"id": "P8", "name": "Salmon Crunch",
 "bag_lb": 12}

Relational databases, tables and schema

A relational database keeps data in tables that link to each other. Each row, or record, is one thing, like one customer. Each field, or column, is one detail about it, like the customer's email. The schema is the plan of the whole database: which tables exist, their fields, each field's data type, and how the tables link. Adding a column changes the schema; adding a row just adds data.

You: So the schema is the floor plan, and the rows are the furniture?

Theo: I'm stealing that.

Primary and foreign keys

A primary key is the field that identifies each row, like CustID. No two rows can share it, and it can't be blank. A foreign key is a field in one table that holds another table's primary key, which links the two: ORDERS.CustID points to a row in CUSTOMERS. Store each customer once and point to them, instead of retyping their name on every order.

CUSTOMERS
CustID  Name      City
C01     Ana Ruiz  Lansing
C02     Lee Park  Detroit

ORDERS
OrderID  CustID  Item           Bags
5001     C01     Salmon Crunch  2
5002     C02     Lamb Bites     1
5003     C01     Turkey Mix     3

Constraints

Constraints are rules the database enforces on fields so bad data can't get in. A primary key must be unique and not blank. A foreign key must point to a row that really exists. A price must be filled in and can't be below zero. When a new row breaks a rule, the database refuses it, and that's exactly what you want.

Rosa: So the database will just say no to my duplicate 5003?

Theo: Politely, but firmly. Like Ranger at bath time.

Exam tip Ana typed three ways? Store her once in CUSTOMERS and let ORDERS point to C01.

Key/value and document databases

Non-relational databases, often called NoSQL, don't use fixed tables. A key/value database stores each value under a unique key and fetches it fast by that key, like a shopping cart saved under cart-1042. A document database stores each record as a document, often JSON, whose fields can differ from one record to the next, and it can search inside those fields, like every product whose color is red.

Theo: The rope toy has a color. The kibble has a bag size. The document database doesn't mind.

Exam tip Fixed tables, links and strict rules? Relational. Fast lookup by one key? Key/value. Records with different fields? Document.

Structured, semi-structured, non-structured

Data comes in three shapes:

• Structured: a fixed layout where every row has the same columns, like an orders table or a comma-separated list.
• Semi-structured: labeled fields that can change from item to item, like a JSON (JavaScript Object Notation) export where toys have a color field and kibble has a bag size field.
• Non-structured: no set fields at all, like photos, videos and recorded calls.

Relational databases: schema, tables, rows, fields

A relational database stores structured data in tables that link to each other, and it's managed with SQL (Structured Query Language). Each row is a record and each column is a field. The schema is the plan: which tables exist, their fields and data types, and how they link. Adding a 'Delivery notes' column changes the schema; adding an order adds a record.

ORDERS
OrderID | CustID | Bag size
5001    | C101   | 30 lb
5002    | C102   | 15 lb
5003    | C101   | 30 lb

Exam tip The header line names the fields. It isn't a record, so this table holds 3 records.

Primary keys and foreign keys

A primary key is a field whose value is unique and never empty, so it identifies exactly one row: OrderID in ORDERS, CustID in CUSTOMERS. Names and cities repeat, so they make poor keys. A foreign key holds values from another table's primary key to link the tables. CustID in ORDERS is a foreign key: C101 appears twice because one customer has two orders.

• Adding a second order 5002: rejected, because primary keys must be unique.
• Adding an order for customer C999, who doesn't exist: rejected by the foreign key.

Constraints

Constraints are rules on a table that the database enforces on every save. 'Price must not be empty' and 'price must be zero or more' stop bad data before it gets in. A report could only spot bad prices after they're saved. Primary and foreign keys are constraints too.

What non-relational means

Non-relational databases store data without fixed, linked tables. They're flexible and fast for certain jobs, but they don't enforce links between tables the way relational keys do. When the finance team needs every payment to match a real order, with the database enforcing it, they need a relational database.

Key/value databases

A key/value database stores each value under a unique key and finds it very fast by that key, like a coat check ticket. The web store saves each shopper's cart under its cart ID, like cart-1042, and fetches it by that ID. It never needs to search inside carts, so key/value fits. Most key/value stores treat the value as one block and can't search inside it.

KEY          VALUE
cart-1042 -> {2 bags Salmon, 1 toy}
cart-1043 -> {1 bag Lamb}

Document databases

A document database stores each record as a document, usually JSON (JavaScript Object Notation), and each document can have its own fields. Toys have colors, kibble has bag sizes, and beds have dimensions, all in one catalog. Unlike most key/value stores, it can search the fields inside documents, such as every product whose color is red.

{ "name": "Squeaky Bone", "color": "red" }
{ "name": "Salmon Crunch", "bagLb": 30 }

Exam tip Fetch by ID only = key/value. Search inside varied records = document. Strict links between tables = relational.