Understanding Relational Databases Through Spreadsheets

Hello! We’ll begin your SQL course with the structure that SQL works on: relational tables. In this first module, you’ll build that foundation before connecting to PostgreSQL on Windows. Today you’ll use a spreadsheet as a reference point, then see where the comparison stops being reliable. By the end, you should be able to look at a small database and identify its tables, rows, columns, data types, primary keys, and foreign keys.

From spreadsheet sheets to database tables

Imagine a spreadsheet used to track customers. Its header row might contain customer ID, name, and email. Each row beneath the headers describes one customer. A database table looks similar: it has named columns, and each row holds one set of values for those columns.

Read the opening of PostgreSQL’s Table Basics before looking at an example. Pay particular attention to two cautions that are easy to miss when thinking in spreadsheet terms.

Here is a small customers table:

The table concerns customers. A column describes the same kind of fact in every row: email, for instance, holds each customer’s email address. A row brings the facts about one customer together. The value maya@example.com is one value at the intersection of Maya’s row and the email column.

The spreadsheet comparison gives us a useful starting vocabulary:

But a spreadsheet’s row number is not a customer identifier. Maya might appear on spreadsheet row 2 today and row 9 after sorting. Likewise, a database table does not promise that its rows will come back in insertion order, alphabetical order, or any other order unless a query explicitly requests sorting. And merely having rows does not guarantee that each represents a distinct real-world customer. Keys help us establish reliable identity.

Columns have data types

A spreadsheet may let you put a date, a number, and a word into cells under the same heading. In PostgreSQL, each column is declared with a data type: a rule about what kind of value it holds and how that value can be used. Read the next part of the same documentation with that distinction in mind.

Suppose we also keep an orders table. Its columns might have these types:

The customers.name and customers.email columns could use text. A customer_id is an integer even though you would not ordinarily add two customer IDs together: its purpose is identification, not measurement. A total, by contrast, is an amount on which calculations make sense.

A type describes what a column can hold; a column name describes what its values mean. Two columns can both be integers while serving entirely different purposes. PostgreSQL also has types such as time and timestamp; you need not memorize its full type catalogue yet.

Why more than one table?

A single spreadsheet might put a customer’s name and email alongside every order they place. That seems convenient until the customer places many orders: the same details are copied repeatedly. If their email changes, which copy should be updated?

Watch Eddie Woo work through that problem. His example starts with an order-tracking spreadsheet and develops it into separate customer and order tables.

Here are two tables following that approach. The customers table above holds each customer’s details once; orders records the purchases:

The first two order rows both contain customer ID 101. Looking that ID up in customers tells us that Maya placed both orders. We have not copied Maya’s name and email into each order row. Ava, customer 103, has no order row yet; she still exists in customers.

This is the central idea behind a relational database: information can be stored in separate tables and related through values they share. A table is usually organized around one subject—customers in one table, orders in another—rather than being one enormous grid of repeated details.

Keys give rows identity and connect tables

The two appearances of customer_id have different jobs. The diagram below shows that distinction. Its column names use capitals, while our examples use lowercase names; the relationship is the same.

A primary key identifies a row within its own table. In our example, customers.customer_id is the primary key of customers: customer 101 identifies Maya’s row and cannot also identify another customer row. A primary key cannot be missing or duplicated. We would also choose orders.order_id as the primary key of our orders table, so 5001 identifies one particular order. A table can have a primary key made from more than one column, though a single ID column is enough for these examples.

A foreign key expresses a relationship to another table. Here, orders.customer_id refers to customers.customer_id. It answers, “Which customer does this order belong to?” Unlike the primary key in customers, the foreign-key value in orders can repeat: Maya can place several orders. If the relationship is enforced by PostgreSQL, an order cannot name a nonexistent customer ID. Depending on how a column is defined, a missing foreign-key value may also be permitted; our example assumes every order has a customer.

Notice that matching column names alone do not create a foreign key. The relationship has to be defined in the database. Conversely, the column names need not be identical for a relationship to exist. What matters is which column references which key.

A primary key also has a precise limit: it prevents two rows with the same key, not every possible mistake in the data. If Maya were mistakenly entered again under a new customer ID, both IDs would be unique even though the business might regard the records as duplicates. We will return to rules for data quality and database design later in the course.

Putting the picture together

You can now describe the example without relying on spreadsheet position:

• customers and orders are tables holding different subjects.
• Each customer or order entry is a row; email and order_date are columns.
• Column data types distinguish, for example, a date from a numeric amount.
• Each table’s primary key identifies one of its rows.
• orders.customer_id is a foreign key connecting an order to its customer. Its repeated value 101 represents multiple orders by the same customer, not an error.

This vocabulary will recur throughout your SQL studies. Later, you’ll use the key relationship to combine customer and order information in a query. Next, we’ll step back from the tables themselves and see how a PostgreSQL client sends a SQL request to the database server and receives a result.