A table is the basic storage structure of a relational database: a named set of rows that share the same typed columns, where each row describes one instance of an entity and each column holds one attribute of it. In database fundamentals, a table is the practical form of the mathematical relation, and almost every query, constraint and index refers to one.
A table is defined by its schema, which fixes column names, data types, defaults and constraints, while its contents change constantly through inserts, updates and deletes. The DBMS enforces that definition on every write, which is what separates a database table from a spreadsheet grid that accepts any value in any cell.
Anatomy of a database table
Every table combines a structural definition with the data that conforms to it. The definition is written in SQL data definition language, typically with CREATE TABLE, and stored in the system catalog, so the DBMS can validate statements and plan queries before touching any data.
The table below names the main components and what each one controls.
| Component | Purpose | Example |
|---|---|---|
| Column | Named attribute with a fixed data type | email VARCHAR(255) |
| Row | One record, one value per column | A single customer |
| Primary key | Uniquely identifies each row | customer_id |
| Foreign key | References a key in another table | orders.customer_id |
| Constraint | Rule checked on every write | NOT NULL, UNIQUE, CHECK |
| Index | Separate structure that speeds lookups | B-tree on email |
In relational theory a relation has no inherent row order and no duplicate tuples. SQL tables relax the second rule, because a table without a primary key may hold identical rows, but they keep the first: rows come back in a defined order only when a query uses ORDER BY.
How a table is stored on disk
Physically, most relational engines store table rows in fixed-size pages, commonly 8 KB in PostgreSQL and 16 KB in InnoDB, the default MySQL and MariaDB engine. A page holds several rows plus a header and free space, and the engine reads and writes whole pages between disk and its buffer pool in memory.
Engines differ in how they organize those pages. A heap table places rows wherever space is free and relies on separate indexes to find them, while a clustered table stores rows in primary key order inside the key’s B-tree, which makes range scans on that key efficient but makes inserts with random keys more costly.
| Organization | Row placement | Typical engines |
|---|---|---|
| Heap | Any page with free space | PostgreSQL, SQL Server heaps |
| Clustered index | Ordered by primary key in a B-tree | InnoDB, SQL Server clustered tables |
| Columnar | Each column stored separately | Analytical engines and warehouses |
| In-memory | Data kept in RAM structures | Memory-optimized tables |
Wide rows matter. Very large values such as long text or binary data are often moved out of line into separate storage, and a table with many wide columns fits fewer rows per page, which means more I/O per query even when only a few columns are needed.
Tables compared with related structures
Several database objects look like tables in query results but behave differently. Knowing the difference avoids surprises with performance and updates.
| Object | Stores data | Main use |
|---|---|---|
| Base table | Yes, persistently | System of record for an entity |
| View | No, a stored query | Simplify or restrict access to data |
| Materialized view | Yes, a refreshed result | Precomputed aggregates and reports |
| Temporary table | Yes, for a session or transaction | Intermediate results in long jobs |
| Partition | Yes, a slice of one logical table | Managing very large tables by range or key |
A table also differs from a document collection in a NoSQL database. A collection does not force every document to share the same fields, whereas a relational table requires every row to match the declared columns, which trades flexibility for consistency and predictable queries.
Designing a good table
Good table design starts from the rule that one table represents one entity or one relationship. Customers, products and orders each deserve their own table, and a many-to-many relationship such as order lines gets a junction table with two foreign keys.
- Choose a stable primary key, either a natural key that never changes or a surrogate integer or UUID.
- Pick the narrowest correct data type, such as
DATEinstead of text for dates, andDECIMALfor money. - Declare constraints in the database, not only in application code, so every client obeys them.
- Normalize to remove duplication, then denormalize deliberately only where measured performance requires it.
- Use consistent naming, for example singular or plural table names across the whole schema.
Structural changes to large tables need care. Adding a nullable column is usually fast, but changing a column type or adding a column with a computed default can rewrite every row and lock the table on some engines, so production migrations are often done online or in stages.
Common table design mistakes
Most table problems come from treating a table like a spreadsheet. The table below lists frequent symptoms, their cause and the usual fix.
| Symptom | Cause | Fix |
|---|---|---|
| Duplicate rows | No primary or unique key | Add a key and remove duplicates |
| Columns like phone1, phone2 | Repeating group in one row | Move values to a child table |
| Orphaned child rows | Missing foreign key | Add the constraint with suitable delete rules |
| Dates sorted wrongly | Dates stored as text | Use a native date or timestamp type |
| Slow full scans | No index on filter columns | Index columns used in frequent WHERE clauses |
Put simply
A database table is like a strict, well-organized ledger. Every page has the same printed columns, and the clerk refuses any entry that does not fit, such as a word written where a date belongs.
Each line in the ledger is one thing, such as one customer or one order, and each column is one fact about it. Because the rules are enforced every time, the data stays clean enough to search, total and connect with other ledgers reliably.
In practice
An online bookstore keeps its orders in a spreadsheet, and staff often type customer names slightly differently, so the same buyer appears under three spellings. When the shop moves to a relational database, the developer creates a customers table with a primary key, a UNIQUE constraint on email and a NOT NULL rule on name.
An orders table then refers to customers through a foreign key, and an order_items table links orders to books. Duplicate customers can no longer be created, an order cannot point to a missing customer, and a monthly sales report becomes a single query joining three tables instead of an afternoon of manual cleanup.