Database Structure and Core Vocabulary

Playback speed

Swipe, scroll or use ← →

From loose values to a table

Almost every later idea in this course is expressed with four words: table, row, column, and data type. Getting them right now means the rest of the course reads cleanly.

PostgreSQL is a relational database management system, which means that it manages data stored in relations. In this context, a relation is essentially the mathematical term for a table. A table is not an unstructured pile of values.

Each table is a named collection of rows, each row in that table has the same set of named columns, and each column has a specific data type.

1 / 17

From loose values to a table

Use the vocabulary precisely, because each word names a different thing.

Word	What it names	Example
Table	One organized collection, under a name	`customers`
Row	One occurrence recorded in that collection	One particular customer
Column	One attribute shared by every row	`signup_date`
Data type	The kind of value a column may store	A date, an integer, a text string

So a designer who needs many customer occurrences that share the same attributes, each attribute holding a declared kind of value, is describing exactly one thing: a customer table whose rows follow named, typed columns. The table supplies the repeated occurrences, and the named columns with their types define the shared structure.

2 / 17

Quick check

A designer needs many customer occurrences that share the same attributes, with a declared kind of value for each attribute. Which structure meets both needs?
1. AA cluster of databases, with one client process dedicated to each attribute
  
  A cluster groups databases and a client is a program; neither one describes repeated customer records.
2. BA stored file whose line order permanently identifies each individual customer
  
  Line order in a file is not how a table identifies its records, and it declares no kind of value.
3. CA customer table whose rows follow named columns, each with its own data type
  
  Right. The table gives the repeated rows, and the named columns with their data types give the shared structure.
3 / 17

The order that counts and the order that does not

New learners often trust what they see on screen. One half of that instinct is safe and the other half is not.

Columns have a fixed order within each row, but SQL does not guarantee the order of rows in a table unless a query explicitly sorts its result for display.

Kind of order	Guaranteed?	Consequence
Column order inside a row	Yes	The third column is the third column every time.
Row order inside a table	No	Two runs of the same query may present rows differently.
Row order in a sorted result	Yes, for that result	Sorting is a request the query makes, not a property of the stored table.

Visual position is therefore not a reliable identity for a stored row. "The first row" is not a record you can point to; it is whatever happened to come back first.

4 / 17

Quick check

What should you assume about the order of the rows stored in a table?
1. AThe insertion position permanently identifies each row that was stored
  
  Insertion position is not a guaranteed identity; the same table can present its rows in another order.
2. BNo row order is guaranteed unless a query sorts its result explicitly
  
  Right. Column order is fixed inside a row, but row order only exists when a query asks for it.
3. CThe order of the columns automatically sorts the rows for every later query
  
  Column order is about position inside a row; it says nothing about how rows are ordered.
5 / 17
Databases and clusters

Tables do not float on their own. They live inside a hierarchy, and the vocabulary for it is easy to mix up.

Tables are grouped into databases. A collection of databases managed by one PostgreSQL server instance is called a database cluster.

Note what cluster does not mean here. It is not a table, not a group of rows, and not a set of connected machines. In PostgreSQL vocabulary it names the collection of databases under one server instance.

6 / 17
Databases and clusters
A single running PostgreSQL server can manage many databases, and a separate database is commonly used for each project or user. That is why "one server" and "one database" are not the same sentence.

The hierarchy gives a useful reading order:
1. A server instance manages a cluster.
2. The cluster contains databases.
3. A database groups tables.
4. A table contains rows described by named, typed columns.
Not every SQL product uses every word in exactly the same way, so this course uses the PostgreSQL meanings whenever it discusses the execution environment.
7 / 17
Quick check

What does the term database cluster mean in PostgreSQL?
1. AThe collection of databases that one server instance manages
  
  Right. Tables are grouped into databases, and the databases under one server instance form the cluster.
2. BThe fixed display order of the rows inside a single result
  
  Row display order is not guaranteed at all, and it names nothing in the object hierarchy.
3. CThe set of client programs reached through one named column
  
  Clients are programs that connect, and a column is an attribute of a table, not a way to group them.
8 / 17
Keep your progress in the app

That’s 3 of 7 quick checks. In the app they stay answered, and every lesson remembers where you left off.

Get the app Keep reading here

The server and the programs that ask it for work

Storage is only half the picture. Something has to receive requests and answer them, and something else has to send them.

PostgreSQL uses a client/server model. The server process manages database files, accepts client connections, and performs database actions on behalf of clients. The client is the program through which a user or another program requests those actions.

Role	Responsibility
Server	Manages the database files, accepts connections, and carries out database actions for its clients.
Client	Sends the request and receives the result; it does not manage the stored files itself.

9 / 17

The server and the programs that ask it for work

Client is a role, not a particular product. A client can be a text-oriented tool, a graphical application, a web server, or a specialized maintenance tool.

So a diagram of one PostgreSQL instance managing several project databases, with a terminal tool and a web application both sending requests, is labeled the simple way: one server manages the cluster, and the terminal tool and the web application are its clients. Neither client becomes a cluster, and neither database becomes a process.

10 / 17
Quick check

Which responsibility belongs to the PostgreSQL server process?
1. AActing as each stored row and fixing the position it holds on screen
  
  A row is managed data inside a table, and screen position is not something the server guarantees.
2. BServing as the visual theme that a graphical client applies to results
  
  Appearance belongs to whichever client is used; the server's job is storage and execution.
3. CManaging the database files, accepting connections, and performing database actions
  
  Right. That is exactly the server's role in the client/server model, carried out on behalf of its clients.
11 / 17
Two machines, two views of the same file name

Client and server describe roles, not necessarily separate computers. But they can run on different hosts and communicate over a TCP/IP network connection, and that possibility has a practical consequence people meet early.

A file visible to the client machine might not be visible to the database server, or it might have a different path there.

Suppose a reporting tool runs on a laptop, PostgreSQL runs on another host, and the report must read a file that exists only next to the reporting tool. The file sits on the client machine, so the server may not see it at all, or may need a different path to reach it. Copying the file where the server can read it, or sending its contents through the client, is the work that the architecture makes necessary.

12 / 17
Quick check

A reporting client runs on one host, PostgreSQL runs on another, and the report needs a file stored only beside the client. What follows from the architecture?
1. AThe server may not see that client-side file, or may need a different path for it
  
  Right. Files reachable on the client machine are not necessarily reachable, or named the same way, on the server machine.
2. BThe file automatically becomes a database cluster that both of the hosts share
  
  A cluster is a collection of databases under one server instance; an ordinary file does not become one.
3. CEach line of the file gains a guaranteed table position before any connection exists
  
  Table positions are not guaranteed even for stored rows, and a file's lines are not table rows.
13 / 17

Keeping the three ideas separate

The beginner's mental model should keep three things apart, because most early confusion comes from merging two of them.

Idea	What it is	What it is not
Data objects	Databases, tables, rows, columns, and their types	Not programs
Server process	The program that manages those objects and executes requests	Not the data it manages
Client interface	The program used to send requests and read results	Not the manager of the database files

Said as one sentence: a server instance manages a cluster, the cluster contains databases, a database groups tables, and a table contains rows described by named, typed columns — while a client, of whatever kind, asks the server to do the work.

14 / 17

Quick check

Which hierarchy uses the PostgreSQL terms correctly?
1. AColumn, client, stored file, cluster, data type, and finally database
  
  Clients and files are not levels of the object hierarchy, and the order given here reverses the real containment.
2. BRow, operating system, client theme, and cluster
  
  An operating system and a client theme are outside the model entirely, and a statement is not a container.
3. CServer instance, cluster, database, table, and row with its columns
  
  Right. One instance manages a cluster of databases, a database groups tables, and rows follow named, typed columns.
15 / 17
Key takeaways
- A table is a named collection of rows organized by named, typed columns, and a row is one occurrence recorded in that collection.
- Column order is fixed inside a row, but row order is not guaranteed unless a query sorts its result for display.
- A PostgreSQL cluster is the collection of databases managed by one server instance, and one server commonly holds a separate database for each project or user.
- Clients request database work; the server accepts connections and performs it — and a client may be a terminal tool, a graphical application, a web server, or a maintenance tool.
- Client and server can sit on different hosts, so a file the client can open may be invisible, or differently named, to the server.
16 / 17
Quick check

Which summary keeps the data objects, the server, and the clients in their right places?
1. AA cluster is one table, rows keep a guaranteed order, and a web server manages the database files
  
  A cluster is a collection of databases, row order is not guaranteed, and a web server acts as a client rather than a manager.
2. BOne instance manages a cluster of databases, and terminal, graphical, and web clients request the work
  
  Right. The instance owns storage and execution, while clients of several kinds send requests to it.
3. CA column is a database, the server is one row, and every client stores its own copy of the files
  
  Columns, rows, and servers are different kinds of thing, and file management belongs to the server alone.
17 / 17
7 quick checks · then the test

In the app, finishing the quick checks opens this lesson’s 10-question test, and the ones you miss come back exactly when you’re about to forget them.

Take this lesson’s test → Next lesson: A SQL Workflow from Connection to Result Get the app

← Previous Keep your progress · Get the app Next card →

The whole course, on your phone

Lessons you can read, audio you can listen to on the way to work, and practice that remembers what you got wrong.

Get the app

Database Structure and Core Vocabulary

From loose values to a table

The order that counts and the order that does not

Databases and clusters

Keep your progress in the app

The server and the programs that ask it for work

Two machines, two views of the same file name

Keeping the three ideas separate

Key takeaways

7 quick checks · then the test

The whole course, on your phone