Tables, Rows & Columns

The basic grid shape — like a spreadsheet — that relational databases use to organize data.

What is it?

Once you know a database stores organized data, the next question is: organized how? In a relational database, data is organized into tables, and each table looks a lot like a spreadsheet.

Each table has columns, which define what pieces of information are tracked (like name, email, age) and what type of data each one holds. Each table also has rows, where every row is one individual record — one specific user, one specific order, one specific product. Every row in a table has a value for every column (or is explicitly left empty).

A table named users with columns id, name, and email might have one row per actual person who signed up.

Explain like I'm 10

Think of a table like a class attendance sheet. The columns are the headers across the top — name, student ID, grade — and each row underneath is one specific student's information filled into those same columns.

Examples

A users table

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

This defines the shape of the table: every row stored here will have exactly these four columns, each holding a specific kind of value.

What the data actually looks like

id | name    | email               | age
---+---------+---------------------+----
1  | Amara   | amara@example.com   | 29
2  | Kenji   | kenji@example.com   | 34
3  | Priya   | priya@example.com   | 41

Each row is one complete record, and every row lines up under the same set of columns defined by the table.

How it works

When a table is created, you (or whoever designed the schema) decide its columns up front: their names and the type of value each will hold (text, integer, date, and so on). From then on, every row added to that table must fit that same shape — one value per column, of the right type.

The database stores rows efficiently on disk and keeps track of the table's structure (called its schema) so it always knows what a row is supposed to look like.

Why does it exist?

Giving every row in a table the exact same shape is what lets the database reason about the data reliably — it always knows, for the users table, that column 3 is an email and should be treated as text, without checking each row individually. That consistency is also what makes fast searching and predictable querying possible.

When to use it

Reach for the rows-and-columns model whenever your data is naturally made of many similar records that share the same fields — users, orders, products, log entries. That covers the vast majority of application data.

When not to use it

If your data is wildly irregular — every record has a completely different, unpredictable set of fields — forcing it into fixed columns can be awkward, and a more flexible, document-style structure (as used in some NoSQL databases) may fit better.

Common mistakes

  • Confusing a column with a row — a column is a field shared across all records, a row is one specific record.

  • Trying to store multiple unrelated pieces of information crammed into a single column instead of splitting them into their own columns.

  • Assuming every row must physically look identical in the raw file — the database, not the disk layout, guarantees the row shape.

Practice exercises

  1. Easy:

    Design the columns for a 'books' table in a personal library app. List each column name and what kind of value it holds.

  2. Medium:

    Write out (as a small text table) three example rows that would fit your 'books' table from the previous exercise.

  3. Hard:

    Explain why storing 'first name and last name' as one combined column is usually a worse design than two separate columns.

Interview questions

What is the difference between a row and a column in a database table?

A column defines one field shared by every record in the table (like email); a row is one individual record with a value for each column.

What is a table's schema?

The definition of a table's structure — its columns, and the type of data each column holds.

Why does every row in a table need to follow the same set of columns?

So the database can reliably interpret and search every row the same way, which is what makes fast, predictable queries possible.

What does it mean for a column to hold `NULL`, versus an empty string or the number 0?

NULL means 'no value, unknown, or not applicable' — it is a distinct marker, not the same as an empty string (a real, zero-length text value) or 0 (a real number). Comparisons and calculations treat NULL specially instead of as 'nothing at all'.

Why would you mark a column `NOT NULL`?

So the database itself rejects any row that doesn't supply a real value for that column, guaranteeing every row always has an actual value there instead of relying on application code to remember to set it.

If a column has no `NOT NULL` constraint, what happens if an INSERT statement omits it?

The database stores NULL there (or the column's DEFAULT value, if one is defined) instead of raising an error.

What's the difference between a column's `DEFAULT` value and simply allowing `NULL`?

DEFAULT supplies a real, concrete value automatically when none is given, so the column still ends up holding actual data. Allowing NULL instead permits 'no value' to be stored explicitly. A column can have neither, either, or — less commonly — both configured.

Why is storing money as an integer number of cents safer than storing it as a floating-point number of dollars?

Binary floating-point numbers can't represent many decimal fractions exactly, so arithmetic like 0.10 + 0.20 can silently produce a value that isn't exactly 0.30. Integers avoid this entirely, since a whole number of cents has no fractional part to round incorrectly.

Why does declaring `age` as `INTEGER` instead of `TEXT` matter, beyond just being 'a number'?

It lets the database enforce that only valid numeric values are stored, compare and sort values numerically rather than character-by-character, and use numeric operations like age + 1 directly without first converting anything.

What would go wrong if `age` were stored as `TEXT` instead of `INTEGER`?

Sorting and comparisons would happen character-by-character instead of numerically, so the text '9' would sort after '10' and '80' (because '1' comes before '9' as a character), and arithmetic like age + 1 wouldn't reliably work without converting the value first.

What breaks if you cram multiple unrelated pieces of information into one column, like a single text value holding a name, age, and city together?

Nothing stops you at the database level since it's just text, but you lose the ability to filter, sort, or update any one piece of that information independently — every query needing just the age or just the city would first have to parse the combined value back apart.

Why is splitting 'first name' and 'last name' into two separate columns usually better than one combined column?

It lets you sort, search, and format by either part independently — for example, alphabetizing by last name — without parsing a combined string every time, which gets error-prone with names that have multiple parts, prefixes, or suffixes.

Can you add a new column to a table that already has rows in it? What happens to those existing rows for the new column?

Yes, using ALTER TABLE ... ADD COLUMN. Existing rows get NULL (or the column's DEFAULT value, if one is specified) in the new column, since there's no way to know retroactively what value they should have had.

Why can't two rows in the same table have a different set of columns?

Because the table's schema fixes the set of columns once for the whole table — every row is a record in that structure, so the database, and every query written against it, can rely on a given column always meaning the same thing for every row.

What's a practical downside of making too many columns on a table nullable 'just in case'?

It becomes ambiguous whether a NULL means 'this genuinely doesn't apply' or 'nobody ever set this,' and every query touching that column has to account for NULL possibly showing up, adding complexity and bugs throughout the application.

Give an example where `NULL` in a column is the correct design choice rather than a mistake.

A middle_name column being NULL for someone with no middle name is legitimate — it genuinely doesn't apply — unlike, say, an email column marked NOT NULL being left empty by accident, which should never be allowed to happen.

Does `WHERE middle_name = ''` match rows where `middle_name` was never set (is `NULL`)?

No. An empty string is a real, zero-length value and is not equal to NULL — a row where middle_name is NULL won't match = '', and in fact it wouldn't match = NULL either; finding it requires IS NULL.

What does it mean that a table's schema is decided 'up front,' and who typically decides it?

Before any data is inserted, whoever designs the table chooses its columns, names, and types via CREATE TABLE. From then on every row added must conform to that shape, though the schema can still be changed later with commands like ALTER TABLE.

Why might a flexible, document-style structure fit better than fixed columns for some data?

When records are naturally irregular — each one might have a completely different, unpredictable set of fields — forcing them all into the same fixed columns leads to lots of unused NULL columns or awkward workarounds; a document model lets each record carry only the fields it actually needs.

In `CREATE TABLE users (id INTEGER, name TEXT, email TEXT, age INTEGER);`, does the order the columns are declared in matter for storing or querying data?

Not semantically — every clause (SELECT, WHERE, SET, and so on) can refer to columns by name, so declaration order is mostly a readability choice. It does matter, though, for statements like INSERT ... VALUES that supply values positionally with no explicit column list.

Are `age = NULL` and `age = 0` the same thing for a row?

No. 0 is an actual, known value — the age is genuinely zero — while NULL means the age is unknown or was never recorded. Treating them as interchangeable would incorrectly imply you know something you don't.

Why does the database, rather than the raw disk layout, guarantee that every row matches the declared columns?

Rows are stored internally however the engine finds efficient — variable-length encoding, compression, and other storage tricks — so no two rows need to occupy identical physical bytes. The guarantee that every row conceptually has a value for every column comes from the schema the engine enforces, not from the file layout.

What's the risk of designing a `tags` column that holds a comma-separated list of values?

You can't easily filter for 'rows containing tag X' with a simple, index-friendly condition, can't guarantee uniqueness or consistent formatting within the string, and any query working with individual tags first has to split the string apart — a related table (or an array/JSON type, if supported) is a better fit.

Why does storing a date in a proper `DATE` or `TIMESTAMP` type matter, rather than storing it as `TEXT`?

A real date type lets the database validate that the value is an actual calendar date, compare and range-filter dates correctly regardless of formatting, and use date-specific operations like extracting the year. With a plain TEXT column, correctness depends entirely on consistent formatting, and nothing stops an invalid value like '13/45/2026' from being stored.

Does every database have a native `BOOLEAN` type?

No — some databases, like SQLite, have no distinct boolean storage type and represent true/false as the integers 1 and 0 instead. The value still behaves like a boolean in queries, but the underlying representation can differ by database.

What is a `CHECK` constraint, and how does it relate to schema design?

A rule attached to a column or table that the database enforces on every insert or update, beyond just type and nullability — for example, CHECK (age >= 0) rejects any row that would set a negative age, letting a basic business rule live in the schema itself instead of relying on every piece of application code to remember to validate it.