Tables, Rows, and Columns

Last Updated: June 12, 2026 • 15 min read

1. Introduction

Tables are the fundamental building blocks of every relational database. Understanding their anatomy — tables (relations), rows (tuples), columns (attributes), and data types (domains) — is essential before writing any SQL query.

2. Why It Matters

Every SQL query you write — from a simple SELECT to a complex multi-table JOIN — operates on tables. Misunderstanding table structure leads to incorrect queries, poor schema design, and data integrity bugs. Interviewers test whether you can articulate the precise meaning of tables, tuples, attributes, and domains because these concepts underpin everything else in SQL.

Schema design: Choosing the right columns and data types determines storage efficiency and query performance.
Query correctness: Understanding that rows are unordered prevents bugs from relying on insertion order.
Data integrity: Proper domain constraints (data types + CHECK constraints) prevent invalid data from entering the database.

3. Real-World Analogy

Think of a table as a spreadsheet in Google Sheets. The spreadsheet has a name ("Q1 Sales"), column headers (Date, Product, Amount), and each row is one sales transaction. But unlike Google Sheets, a database table enforces strict rules: every cell in a column must be the same type, no two rows can be identical (if a primary key exists), and the column headers can't change on the fly.

4. How It Works

A relational table has three core dimensions:

Table (Relation): A named, two-dimensional structure storing data about one entity type. Key properties: fixed structure, unordered rows, unique rows, atomic values.
Row (Tuple): One instance of the entity. A users table row contains values for every defined column: id=42, username="alice", email="alice@example.com".
Column (Attribute): A single property with a defined data type. Every row must have a value for every column (possibly NULL).

Two important metrics:

Cardinality: The number of rows in a table. A table with 1 million rows has cardinality 1,000,000.
Degree: The number of columns. A users table with 8 columns has degree 8.

5. Internal Architecture

PostgreSQL stores tables on disk as heap files — collections of fixed-size pages (typically 8 KB each). Each page contains row headers and row data (called "tuples" internally). When you INSERT a row, PostgreSQL finds a page with enough free space and writes the tuple. When you SELECT, the storage engine scans pages (or uses indexes to jump directly to relevant pages).

6. Visual Explanation

7. Practical Example

Creating a well-designed products table with appropriate data types and constraints:

Querying table metadata to inspect structure:

8. Common Mistakes

Common Pitfall

Using VARCHAR(255) out of habit from MySQL defaults. In PostgreSQL, TEXT has no performance penalty vs. VARCHAR and is preferred for most string columns.

Storing dates as TEXT: Prevents date arithmetic, range queries, and timezone handling. Always use DATE, TIMESTAMP, or TIMESTAMPTZ.
Using FLOAT for money: Floating-point precision errors cause penny-rounding bugs. Use NUMERIC/DECIMAL for financial data.
Assuming row order: Without ORDER BY, the database can return rows in any order. Never rely on insertion order for correct results.

9. Quick Quiz

Q1: What is the difference between cardinality and degree?

Cardinality = number of rows. Degree = number of columns.

Q2: Why should you use TIMESTAMPTZ instead of TIMESTAMP?

TIMESTAMPTZ stores timezone-aware timestamps, preventing bugs when your application serves users across multiple timezones.

10. Scenario-Based Challenge

Scenario

You're designing a table for customer reviews. Each review has a rating (1-5), a text comment, and a timestamp. What data types would you choose for each column? What constraints would you add? Consider: should rating be INTEGER or NUMERIC? Should the comment be TEXT or VARCHAR(500)?

11. Debugging Exercise

The following table has three data type problems. Can you spot them?

Fixes: (1) Use SERIAL or UUID instead of TEXT for the primary key. (2) Use NUMERIC(10,2) instead of FLOAT for monetary values. (3) Use TIMESTAMPTZ instead of VARCHAR for dates.

12. Interview Questions

Q: What is the difference between a relation and a table?

In relational theory, a "relation" is the mathematical concept; a "table" is its physical implementation in SQL. A relation is a set (unordered, unique elements), while SQL tables can have duplicate rows unless constrained.

Q: When would you use NUMERIC vs INTEGER vs FLOAT?

INTEGER for whole numbers (counts, IDs). NUMERIC for exact decimal values (money, percentages). FLOAT for approximate scientific calculations where slight precision loss is acceptable.

Q: What does "atomic values" mean in First Normal Form?

Each cell contains a single, indivisible value. A cell should not contain "New York, Los Angeles" (two cities) — that should be two separate rows or a junction table.

13. Production Considerations

Column order in indexes: The order of columns in a composite index affects which queries can use it. Put the most selective or frequently filtered column first.
TOAST storage: PostgreSQL stores large TEXT/BYTEA values in a separate TOAST table. This is automatic but means very large text fields add an extra lookup cost.
Partitioning: For tables with millions of rows, consider table partitioning (by date, by range) to improve query performance and simplify maintenance (dropping old partitions).
NOT NULL defaults: In production, prefer NOT NULL with sensible defaults over nullable columns — it makes queries simpler and prevents NULL-related bugs.