Tables, Rows, and Columns: The Relational Database Model

Relational databases represent information using tables. The layout resembles a spreadsheet, but database tables carry stronger definitions and relationships.

A table represents a type of entity or relationship, each row represents one record, and each column represents a defined attribute.

Keys identify rows and connect records across tables.

A concrete example: customers

A customers table might contain:

The table describes one kind of thing: customers.

Each row is one customer record. Each column has a stable meaning across rows. The customer_id distinguishes one customer from another.

Tables model entities and relationships

An entity is a distinct concept the system needs to remember, such as:

• Customer
• Product
• Employee
• Invoice
• Course

Not every table is a physical thing. A table may represent an event, such as a payment, or a relationship, such as a student's enrollment in a course.

Good table boundaries reflect business concepts. A table named misc_data with dozens of unrelated fields avoids design decisions rather than solving them.

Rows represent individual records

A row, also called a record or tuple, contains values describing one instance.

All rows in a relational table follow the same column definition, though some values may be nullable.

Rows do not have a meaningful visual order unless a query explicitly requests one. The first row shown today is not guaranteed to remain first tomorrow.

To request order, use a defined field:

SELECT *
FROM customers
ORDER BY joined_at DESC;

Treating storage order as business meaning creates fragile software.

Columns define attributes and types

A column has:

• A name
• A data type
• Optional constraints
• A meaning documented by the schema and application

For example:

email VARCHAR(320) NOT NULL

The type says the value is text. NOT NULL says every row must provide it.

Types allow databases to validate operations. Dates can be compared as dates, numbers can be aggregated, and invalid values can be rejected before they spread.

Primary keys identify rows

A primary key uniquely identifies each row.

customer_id BIGINT PRIMARY KEY

A useful primary key is:

• Unique
• Never missing
• Stable over the record's lifetime
• Efficient to reference

Names and email addresses often make poor primary keys because they can change or collide. Systems commonly use generated integers or UUIDs and enforce separate uniqueness rules for business fields.

The identifier has meaning inside the system even if users never see it.

Foreign keys create relationships

Suppose orders belong to customers:

orders.customer_id is a foreign key referring to customers.customer_id.

This expresses a one-to-many relationship: one customer can have many orders, while each order belongs to one customer.

A foreign-key constraint can prevent an order from referring to a customer that does not exist.

One-to-one, one-to-many, and many-to-many

Common relationship shapes are:

• One-to-one: One user has one profile record.
• One-to-many: One author writes many articles.
• Many-to-many: Many students take many courses.

A many-to-many relationship uses a junction table:

Each row in enrollments represents a relationship. It can also store attributes of that relationship, such as enrollment date or final grade.

Constraints preserve rules

Database constraints can enforce:

• NOT NULL for required values
• UNIQUE for values that must not repeat
• CHECK for allowed ranges or conditions
• FOREIGN KEY for valid relationships
• PRIMARY KEY for identity

quantity INTEGER CHECK (quantity > 0)

Application validation provides friendly feedback, but database constraints remain a final line of protection when multiple applications or scripts write data.

Normalization reduces conflicting copies

Suppose every order row repeats the customer's name and email. When an email changes, old and new orders may disagree.

Normalization separates concepts into related tables so each fact has an appropriate home:

• Customer contact information belongs in customers.
• Order-specific values belong in orders.
• Product quantities belong in order_items.

This reduces update anomalies and duplication.

Normalization is not a command to create the maximum possible number of tables. The goal is consistent ownership of facts.

Denormalization can improve specific reads

Some systems deliberately duplicate values to reduce joins or serve analytical workloads.

For example, an order may preserve the shipping address used at purchase even if the customer's current address later changes. That is not accidental duplication; it is a historical fact owned by the order.

Analytical tables may also precompute totals for faster reports.

Denormalization should be explicit about:

• Which copy is authoritative
• How copies stay synchronized
• Whether historical differences are intended

Performance improvements without those rules create inconsistent data.

Null means absent or unknown

NULL represents the absence of a value, not zero or an empty string.

SQL uses three-valued logic around null. This query does not find missing values:

WHERE middle_name = NULL

The proper check is:

WHERE middle_name IS NULL

Nullable columns should have a clear meaning. If delivered_at is null, does that mean not delivered, unknown, or not applicable? Schema documentation and status fields may be needed.

Schema changes require migrations

Applications evolve. A table may need a new column, constraint, or relationship.

A migration is a controlled change to the database schema and sometimes its existing data.

Safe migrations consider:

• Compatibility with old and new application versions
• Default values
• Large-table performance
• Rollback or forward-repair plans
• Backfilling existing rows
• Deployment order

The schema is a living contract shared by software and stored data.

Knowledge check

1. What concept should one table usually represent?
2. Why should applications not rely on a table's apparent row order?
3. What is the difference between a primary key and a foreign key?
4. How is a many-to-many relationship represented?
5. Why can repeating customer contact data in every order cause inconsistency?

The one idea to remember

Relational tables organize consistent records: rows represent instances, columns define attributes, primary keys provide identity, and foreign keys connect facts without losing their proper ownership.