Tables, Rows, Columns, Schemas, and Namespaces

Learning outcomes

Chapter 1 introduced relations, tuples, attributes, and domains as logical ideas. SQL products implement those ideas through named database objects. This lesson connects the formal vocabulary to the structures you see in tools, scripts, and application code.

From the relational model to SQL objects

A relation is an abstract set of tuples. A SQL table is a persistent database object with a name, ordered column definitions, declared types, constraints, and a changing collection of rows. The two concepts are closely related, but they are not perfectly identical. SQL permits duplicate rows unless constraints prevent them, supports NULL, and exposes implementation details such as column order.

Tables, rows, and columns

Consider a table named learner. Its definition might declare learner_id, full_name, and joined_at. Every stored learner row follows that shape, but each row contains different values.

CREATE TABLE learner (    learner_id INTEGER PRIMARY KEY,    full_name  TEXT NOT NULL,    joined_at  TEXT NOT NULL);INSERT INTO learner (learner_id, full_name, joined_at)VALUES    (1, 'Nadia Rahimi', '2026-08-01'),    (2, 'Omar Haddad',  '2026-08-02'),    (3, 'Lina Chen',    '2026-08-04');

The column list belongs to the table definition. The three parenthesized value groups create three rows. The primary-key constraint establishes that each learner_id must uniquely identify one row.

Table definition versus table state

The schema of a table—here meaning its structure—changes relatively rarely. The state or instance of the table changes whenever rows are inserted, updated, or deleted. Keeping these ideas separate is essential when reviewing migrations and debugging data problems.

Schemas and namespaces

A namespace is a context in which names must be unique. A schema is commonly used as a database namespace that groups tables, views, routines, and other objects. In PostgreSQL, for example, sales.customer and support.customer can coexist because the tables belong to different schemas.

CREATE SCHEMA sales;CREATE SCHEMA support;CREATE TABLE sales.customer (    customer_id INTEGER PRIMARY KEY,    display_name VARCHAR(100) NOT NULL);CREATE TABLE support.customer (    customer_id INTEGER PRIMARY KEY,    priority_code VARCHAR(20) NOT NULL);SELECT * FROM sales.customer;

A qualified name communicates intent and avoids depending on a session’s default search path. Production SQL often qualifies important objects, especially in migrations, reporting queries, and cross-schema integrations.

SQLite’s namespace model

SQLite does not implement CREATE SCHEMA like PostgreSQL or SQL Server. Every connection has the main database, a temporary temp database, and optionally attached database files. The attachment name acts as a namespace.

sqlite3 practice.dbATTACH DATABASE 'analytics.db' AS analytics;CREATE TABLE main.learner (    learner_id INTEGER PRIMARY KEY,    full_name TEXT NOT NULL);CREATE TABLE analytics.daily_metric (    metric_date TEXT PRIMARY KEY,    active_learners INTEGER NOT NULL);SELECT * FROM main.learner;SELECT * FROM analytics.daily_metric;PRAGMA database_list;

This is useful for learning qualification and for moving data between SQLite files, but it is not a substitute for the richer schema, permission, ownership, and search-path features of a server DBMS.

Naming database objects

Names are part of the database interface. Prefer predictable identifiers that survive across operating systems, drivers, and SQL dialects:

• use concise snake_case names such as course_enrollment;
• choose singular or plural table names consistently;
• avoid spaces, punctuation, and names that require quoting;
• avoid reserved words such as user, order, and group unless your dialect and conventions deliberately handle them;
• name identifiers by meaning, not by display labels;
• qualify names when more than one namespace could contain the object.

Lab: inspect the database catalogue

Open the practice environment from Chapter 1 and run the following script. It creates two related tables and then asks SQLite to describe them.

PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS enrollment;DROP TABLE IF EXISTS course;CREATE TABLE course (    course_id INTEGER PRIMARY KEY,    course_code TEXT NOT NULL UNIQUE,    title TEXT NOT NULL);CREATE TABLE enrollment (    learner_id INTEGER NOT NULL,    course_id INTEGER NOT NULL,    enrolled_at TEXT NOT NULL,    PRIMARY KEY (learner_id, course_id),    FOREIGN KEY (course_id) REFERENCES course(course_id));SELECT name, type, sqlFROM sqlite_schemaWHERE type = 'table'  AND name NOT LIKE 'sqlite_%'ORDER BY name;PRAGMA table_info('course');PRAGMA foreign_key_list('enrollment');

sqlite_schema contains metadata about tables, indexes, views, and triggers. PRAGMA table_info reports columns, declared types, nullability, defaults, and primary-key participation. These commands inspect definitions; they do not query application rows.

Common mistakes

Calling a column a field in every context

Field is common informal language, but column is more precise for relational tables. A field can also mean one component inside a document, message, form, or programming-language object.

Treating row order as stored meaning

Rows have no guaranteed presentation order unless a query uses ORDER BY. A visual tool may repeatedly show the same order, but applications must not rely on it.

Using schemas only as folders

Schemas can also establish ownership, permissions, search paths, deployment boundaries, and naming isolation. They are architectural objects, not merely visual organization.

Creating one table for unrelated facts

A table should have a coherent row meaning. Mixing customers, orders, and support tickets into one wide table creates ambiguous columns and integrity problems.

Checkpoint and practice

Summary and next lesson

Tables provide named, constrained structures; rows represent occurrences; columns represent shared properties; and schemas or other namespaces organize object names. The next lesson examines the data-type families that define which values a column can meaningfully hold and which operations are valid.

References

• SQLite: CREATE TABLE
• SQLite: The Schema Table
• SQLite: ATTACH DATABASE
• PostgreSQL: Schemas