9.3. Tables, Rows, and Columns

Understanding Tables, Rows, and Columns

In relational databases, almost everything you work with is built on top of tables, rows, and columns. If you understand these three ideas clearly, the rest of SQL and database work becomes much easier.

This chapter will focus only on these basic building blocks, not on advanced topics like keys, relationships, or normalization. Those will come in later chapters.

The Table as a Spreadsheet

A helpful way to think about a table is to imagine a spreadsheet in Excel or Google Sheets.

A table is like a sheet.
Rows are like the horizontal records.
Columns are like the vertical fields.

A table stores multiple records of the same type.

Examples of tables you might have in an application:

Table name	What it stores
`users`	All user accounts
`products`	All products in a store
`orders`	All customer orders
`blog_posts`	All blog articles
`comments`	All comments on blog posts

Each table has:

A name (for example users).
A set of columns with defined types and names (for example id, email, created_at).
A set of rows, where each row is one record that fits the column definitions.

Important rule: Every row in a table has exactly the same set of columns, defined once in the table schema. You do not add or remove columns for individual rows.

Columns: Defining the Shape of Your Data

A column represents one attribute or property that every row in the table can have.

For example, in a table of users, typical columns might be:

Column name	Example values	Meaning
`id`	`1`, `2`, `3`	Unique identifier for a user
`email`	`alice@example.com`	User email address
`name`	`Alice`, `Bob`	User's display name
`age`	`25`, `32`, `19`	User's age in years
`created_at`	`2026-08-27 10:15:23`	When the user was created

Every column has at least:

A name (identifier).
A data type (for example integer, text, date).
Often constraints (for example cannot be null, must be unique).
Constraints are covered in another chapter, so we will only mention them briefly here.

Think of a column as a named field definition that says: "Every user record will have an email, and it must be text."

Example: Product Table Columns

Let us imagine an products table for an online store.

Possible columns:

Column name	Data type	Example values	Description
`id`	integer	`1`, `2`, `3`	Product identifier
`name`	text	`T-shirt`, `Laptop`	Product name
`price`	numeric	`19.99`, `1200.00`	Product price
`in_stock`	integer	`0`, `10`, `500`	How many items are in stock
`created_at`	timestamp	`2026-08-27 09:00:00`	When the product was first added
`is_active`	boolean	`true`, `false`	Whether the product is visible for sale

Key idea: A column describes one kind of data, and its data type controls what values are allowed and how they are stored.

Rows: Individual Records

A row is a single record, an instance of the thing the table represents.

In the products table example, each row is one product.

Let us look at some example rows for a users table.

Example: `users` Table Data

Columns:

id (integer)
email (text)
name (text)
age (integer)
created_at (timestamp)

Rows:

id	email	name	age	created_at
1	alice@example.com	Alice	25	2026-08-26 09:15:00
2	bob@example.com	Bob	32	2026-08-26 10:30:00
3	charlie@example.com	Charlie	19	2026-08-27 08:05:00

Each row here is one user account.

Reading a Row as a Record

Row 2:

id = 2
email = bob@example.com
name = Bob
age = 32
created_at = 2026-08-26 10:30:00

In programming terms, this row is similar to a dictionary or object:

python

user = {
    "id": 2,
    "email": "bob@example.com",
    "name": "Bob",
    "age": 32,
    "created_at": "2026-08-26 10:30:00"
}

Different languages show this differently, but the idea is always:

Column names are like keys or property names.
Row values are the values for those keys.

Putting It Together: A Table as a Collection of Identical Records

A table is really just:

A set of columns that defines the shape of the data.
A collection of rows that follow that definition.

Visually, you can think of it as:

	Column 1	Column 2	Column 3
Row 1	value	value	value
Row 2	value	value	value
Row 3	value	value	value

In a database:

The set of columns is called the table schema.
The values inside are the data.

Examples of Common Tables

Let us go through concrete examples from a typical web application.

Example 1: `users` Table

Use case: Store information about users who sign up.

Column	Data type	Example value
`id`	integer	`42`
`email`	text	`user@example.com`
`password`	text	`hashed_password_here`
`name`	text	`Jane Doe`
`is_active`	boolean	`true`
`created_at`	timestamp	`2026-08-25 12:00:00`

Example rows:

id	email	password	name	is_active	created_at
1	alice@example.com	`<hash>`	Alice	true	2026-08-20 09:00:00
2	bob@example.com	`<hash>`	Bob	false	2026-08-21 10:15:00

Note: Password hashing and security will be explained in authentication chapters. Here the important part is that each user is one row.

Example 2: `orders` Table

Use case: Store orders placed in an e-commerce system.

Column	Data type	Example value
`id`	integer	`101`
`user_id`	integer	`1`
`total_amount`	numeric	`59.97`
`status`	text	`pending`, `paid`
`created_at`	timestamp	`2026-08-27 11:30:00`

Example rows:

id	user_id	total_amount	status	created_at
101	1	59.97	pending	2026-08-27 11:30:00
102	2	19.99	paid	2026-08-27 11:45:30

Here again:

One row is one order.
Each column stores one aspect of that order.

Relations between orders.user_id and users.id are part of relationships and foreign keys, which have their own chapters later.

Column Data Types and How They Shape Rows

Columns are not just names, they also have types, which define what kind of data can be stored.

Common types:

Type	Example column	Example value
`integer`	`age`	`30`
`numeric` or `decimal`	`price`	`19.99`
`text`	`name`	`"Alice"`
`boolean`	`is_active`	`true` or `false`
`date`	`birth_date`	`2020-01-01`
`timestamp`	`created_at`	`2026-08-27 10:15:00`

When you insert a new row:

The database will check each value against the column type.
If a value does not match, the database will reject the row or convert it if possible.

Example:

age is integer.
Trying to store "twenty" as age will fail.

Rule: The values in each row must follow the types defined by the columns. The table structure controls what is allowed.

Naming Tables and Columns

Good naming makes your database much easier to understand.

Table naming tips

Use plural nouns for tables that store many entities:

users, products, orders, comments.

Use snake_case (all lowercase, words separated by underscores) in most SQL databases:

blog_posts, order_items.

Column naming tips

Use clear, descriptive names:

created_at instead of ca.
total_amount instead of ta.

Use consistent suffixes for timestamps:

created_at, updated_at, deleted_at.

For booleans, use names that read like a question:

is_active, is_admin, has_paid.

Example of consistent naming:

Column	Meaning
`id`	Primary identifier
`created_at`	When the row was created
`updated_at`	When the row was last updated
`deleted_at`	When the row was deleted, if any
`is_active`	Whether the row is currently active

Good names help you, your teammates, and your future self understand your data quickly.

How Tables, Rows, and Columns Map to Code

As a backend developer, you rarely deal with raw rows and columns only, you also map them to code structures.

Mapping to Objects (OOP)

In many languages:

A table maps to a class.
A row maps to an object instance.
A column maps to an attribute.

Example in Python style (not using any ORM yet):

python

class User:
    def __init__(self, id, email, name, age, created_at):
        self.id = id
        self.email = email
        self.name = name
        self.age = age
        self.created_at = created_at
# One row in the database:
# id | email              | name  | age | created_at
# 1  | alice@example.com  | Alice | 25  | 2026-08-26 09:15:00
user = User(
    id=1,
    email="alice@example.com",
    name="Alice",
    age=25,
    created_at="2026-08-26 09:15:00"
)

So when you query the users table, each row often becomes a User object in your code.

ORMs (Object Relational Mappers) make this mapping automatic, and you will learn about them in later chapters.

Visualizing Tables in SQL

You will learn SQL commands like CREATE TABLE in the SQL chapters. For now, here is a very simple example to visualize how columns and rows connect to SQL syntax.

Define a table with columns:

sql

CREATE TABLE users (
    id          SERIAL,
    email       TEXT,
    name        TEXT,
    age         INTEGER,
    created_at  TIMESTAMP
);

This defines the table structure:

Table name: users
Columns: id, email, name, age, created_at
each with a type.

Insert rows:

sql

INSERT INTO users (email, name, age, created_at)
VALUES
  ('alice@example.com', 'Alice', 25, '2026-08-26 09:15:00'),
  ('bob@example.com', 'Bob', 32, '2026-08-26 10:30:00');

Now the table has two rows, exactly like the earlier diagram.

Selecting rows:

sql

SELECT id, email, name, age, created_at
FROM users;

This returns the rows as a result set, again showing how rows and columns appear.

You will study all these commands in detail in the SQL chapters. Here, the goal is just to connect the mental model:

CREATE TABLE defines columns.
INSERT creates rows.
SELECT reads rows and columns.

Common Mistakes When Thinking About Tables

Beginners often run into confusion when designing tables. Here are some issues related directly to tables, rows, and columns.

1. Using One Column for Multiple Values

Bad idea:

A users table with a column phone_numbers that stores "123-456-7890, 987-654-3210" in one text field.

Why this is a problem:

It is hard to search or filter by a specific phone number.
You are hiding multiple logical values inside one column.

Better approach:

Use a separate table, for example user_phones, with one row per phone number.
This relates to relationships and normalization, which are covered later.

2. Too Many Optional Columns

Bad idea:

A users table with many columns where most are empty for most users, for example:

phone_home, phone_work, phone_mobile, phone_emergency, etc.

Often this signals that the design might be improved by splitting into more focused tables.

3. Inconsistent Column Names Across Tables

Example:

created_at in one table.
createdOn in another.
when_created in a third.

This creates confusion and bugs. Try to use the same patterns everywhere.

How Backend Features Reflect in Tables

Different features of your application will often need their own tables.

Here are some examples, focusing on how you would shape the tables with rows and columns.

User Authentication Feature

You might need:

users table for user accounts.

Columns:

Column	Description
`id`	User ID
`email`	User email
`password`	Password hash
`created_at`	When the account was created

Each login or registration event will add or update rows in this table.

Blog Feature