Lesson 04 ~25 min read

Tables, Rows & Columns

The table is the fundamental building block of every relational database. Everything you store, query, and relate lives in tables. Understanding their anatomy precisely is the foundation for everything that follows.

The Relational Model

The relational database model was proposed by E.F. Codd of IBM in his landmark 1970 paper "A Relational Model of Data for Large Shared Data Banks." It remains the dominant model for structured data more than 50 years later.

The core idea is elegant: all data is stored in relations — which we call tables. A table is a two-dimensional structure of rows and columns. Every piece of data has a precise location: a specific column in a specific row of a specific table.

// Formal vs informal terminology

Codd used formal mathematical terms: relation (table), tuple (row), attribute (column). In practice, almost everyone says table, row, and column. Both mean the same thing.

Anatomy of a Table

Let's build a real table from scratch and name every part.

students table

Table name: students
                                                           
┌────────────┬──────────────┬─────┬──────────────┬─────────────┐
│ student_id │ full_name    │ age │ email        │ enrolled_on │  ← Column headers (attributes)
├────────────┼──────────────┼─────┼──────────────┼─────────────┤
│ 1          │ Kondwani Banda  │ 21  │ p@email.com  │ 2025-01-10  │  ← Row 1 (tuple)
│ 2          │ Chimwemwe Moyo│ 23  │ c@email.com  │ 2025-01-11  │  ← Row 2
│ 3          │ Mphatso Phiri│ 20  │ m@email.com  │ 2025-02-01  │  ← Row 3
└────────────┴──────────────┴─────┴──────────────┴─────────────┘
  ↑
  Cell: intersection of one row and one column

Table (Relation)

The container. Has a name, a fixed set of columns, and zero or more rows. Represents one entity type — students, courses, orders.

Column (Attribute)

Defines one property of the entity. Has a name, a data type, and optional constraints. Every row must provide a value for each column (or NULL if allowed).

Row (Tuple/Record)

One instance of the entity. One student, one course, one order. Each row holds one value per column.

Cell

The intersection of one row and one column. Contains exactly one atomic (indivisible) value.

Data Types

Every column has a data type that constrains what values can be stored in it. Choosing the right data type saves storage space, improves performance, and prevents invalid data entering your database.

INT / INTEGER

Whole numbers. Variants: TINYINT (0–255), SMALLINT, INT, BIGINT. Use for IDs, counts, ages.

DECIMAL(p, s)

Exact decimal numbers. DECIMAL(10, 2) stores up to 10 digits with 2 after the decimal. Use for money — never use FLOAT for currency.

VARCHAR(n)

Variable-length text up to n characters. Stores only what it needs. Use for names, emails, descriptions.

CHAR(n)

Fixed-length text, always n characters (padded with spaces). Useful for fixed codes like country codes (CHAR(2)) or phone formats.

DATE / DATETIME

DATE stores YYYY-MM-DD. DATETIME stores date and time. TIMESTAMP stores date-time with timezone awareness.

BOOLEAN

True or false. In MySQL stored as TINYINT(1). In PostgreSQL a native BOOLEAN type exists.

TEXT / BLOB

Large amounts of text (TEXT) or binary data like images (BLOB). Don't use for columns you search or sort — index on VARCHAR instead.

Column Constraints

Constraints enforce rules on column data. They are the database's way of rejecting invalid data before it enters the system — far better than trying to clean bad data after it's in.

sql — column constraints

CREATE TABLE students (
  student_id   INT           PRIMARY KEY AUTO_INCREMENT,
  full_name    VARCHAR(100)  NOT NULL,
  email        VARCHAR(150)  NOT NULL UNIQUE,
  age          INT           CHECK (age >= 16 AND age <= 100),
  enrolled_on  DATE          DEFAULT CURRENT_DATE,
  is_active    BOOLEAN       DEFAULT TRUE
);

NOT NULL

Column must have a value — cannot be empty. Use on any column that is always required.

UNIQUE

No two rows can have the same value in this column. Common on email addresses and usernames.

CHECK

A condition every value must satisfy. Rejects any insert or update that would violate it.

DEFAULT

Value used when no value is provided during INSERT. Reduces the burden on application code.

// Knowledge Check

You're designing a table to store product prices. Which data type should you use to avoid floating-point rounding errors in financial calculations?