Relational Database: How Tables, Rows, and Keys Organize Data

What a table actually is

According to the official PostgreSQL documentation, a table is a set of rows identified by a name, and every row shares the same set of columns, each holding a specific data type. A customers table therefore has one row per customer, with columns like name, address, and tax code; an orders table has one row per order, with columns like date, amount, and the customer who placed it. An application’s tables group together into an actual database, managed by a DBMS such as PostgreSQL or MySQL.

Practical example: an online store keeps two tables, customers and orders. The customers table has one row per person, with columns like name and email. The orders table has one row per purchase, with columns like date, amount, and a column marking whose order it is. That column holds the customer’s identifier — enough to look up the rest of their data.

The keys that hold the data together

Every table has a primary key, a value that identifies exactly one row. Per the PostgreSQL documentation, primary key values must be unique and never null, and a table can have at most one.

The foreign key does the opposite, complementary job: it doesn’t identify rows in its own table, it points to the primary key of another one. In the orders table, the column marking the customer is a foreign key toward the customers table, and it holds the customer’s identifier. If someone tries to insert an order with a customer identifier that doesn’t exist, the system rejects the operation with a constraint violation error — that’s how the model keeps data consistent across tables without manual checks on every insert.

Three ways tables connect

Two tables can connect in three different ways, depending on how many rows of one correspond to how many rows of the other.

Practical example: in a students table and a courses table, a student can attend several courses and a course can have several enrolled students. That’s a many-to-many relationship, different from the link between a customer and their orders, where each order belongs to exactly one customer even though a customer can have many orders.

Where the model came from

The relational model dates back to 1970, when Edgar F. Codd, a researcher at IBM’s San Jose labs, published the paper describing it in Communications of the ACM. In the introduction, Codd applied the mathematical theory of relations to managing large shared data banks, with one precise goal: people using the data shouldn’t need to know how it’s physically organized on the machine. That paper is where the model’s vocabulary comes from, relation for table, tuple for row, domain for column, alongside the definitions of primary key and foreign key. Codd argued the relational model was superior to the hierarchical and network models common at the time, because it describes data according to its natural structure, without adding structures meant only for the machine storing it.

SQL, the language for querying data

Relational databases are queried with SQL, the language that DBMSs such as PostgreSQL largely implement to create tables, insert rows, and run searches. A site written in PHP, JavaScript, or TypeScript talks to the database precisely through SQL, and that’s what lets the web remember every customer, every order, every page visited. Datasets used for machine learning often start out as relational tables too, before being reshaped for training.

The fixed schema behind this model doesn’t fit every use case: relational vs. non-relational databases covers when a NoSQL database such as MongoDB or Redis works better instead.