Can a base table contain data imported from another system?

Yes. Its population process and its classification as a regular table are different questions.

Can a view be updated?

Some views are updatable under a database's rules, with changes affecting underlying data. Others require a different approach.

Does base relation mean the same as a physical file?

No. It is a logical database concept, while file layout is an implementation matter.

AI Glossary / Data engineering and architecture / Beginner

Base relation

Definition

A base relation is a relation represented as stored data in a database rather than defined as the result of a query over other relations. In everyday SQL discussion, a base table is the usual counterpart. Queries and views can derive results from it; business authority requires separate verification.

What it means

An Orders table may hold the recorded facts about orders. A view called OpenOrders can select only those whose status is open. Orders is the base table in this relationship; OpenOrders is derived from it.

The distinction is about the database object and its definition. It does not mean that base-table values were entered manually or never transformed before loading. Applications can populate tables from other sources.

Nor does base mean guaranteed truth. A stored record can be outdated or incorrect. Being a base relation tells you how the database represents it, not whether it is the organization's approved source for a particular decision.

How it works

A base table has a declared structure, with columns, types and any specified constraints. Authorized operations insert, update or delete its rows under the database's transaction and integrity rules.

Queries read that data and produce results. A normal view stores a query definition and presents its result through a table-like interface. A materialized view stores a derived result, so it is an important exception to the oversimplification that all physically stored data must be base data.

Applications can use views to expose selected columns or a stable presentation without requiring every consumer to know the complete underlying schema. Some simple views are updatable in particular systems; others are not. A write through an updatable view can ultimately affect underlying base data.

The logical base relation should also be distinguished from its physical files and indexes. A database can reorganize storage or maintain indexes while presenting the same table structure to its users. A table is not necessarily one directly editable file.

Three practical examples

An order-management database stores order identifiers, customer references and status in a base table used by several operational queries.
A training platform defines a view over its enrollment table to show active participants without duplicating those rows into another manually maintained list.
An analytics team traces a calculated monthly total back through its query to the stored tables that supplied the contributing facts.

Think of it this way

Think of a maintained register and several reports prepared from it. The register supplies the entries; one report lists active records and another counts records by category.

The analogy has limits. A database may hold several relevant registers, and their contents may originate in other systems. A printed report can also become a stored snapshot, much as a materialized view persists derived data. Authority requires additional context.

Worked example

Base and derived data

Consider an invented Orders table with three rows: order O1 has status open and amount 40, O2 is closed with amount 70, and O3 is open with amount 20. A view selecting open orders returns O1 and O3.

A query summing the amounts in that view returns 60. If O3's status is changed to closed and the update is committed, a subsequent view query using a snapshot that sees the update returns only O1, with total 40.

Now suppose a materialized summary was calculated before that change. Its stored total can remain 60 until the system refreshes or otherwise maintains it according to the implementation. Its physical storage does not make it independent base information.

The example separates three concepts: recorded order facts, a query-defined subset and a persisted derived result. Transaction visibility and refresh behavior determine what a particular reader sees at a particular time.

Illustrative case study

A fictional membership organization maintains a base table of membership records and a view for active members. A dashboard team copies the active-member result into a spreadsheet and later treats that copy as the definitive current list.

After several cancellations, the copied list differs from the database view. The team identifies the copy as an extract with an extraction time, rather than describing it as a live representation of membership. It defines a refresh process and confirms which database records own the membership status.

Distinguishing base and derived data helps explain report disagreements. It does not remove the need to check whether the underlying membership updates were correct in the first place.

Common misconceptions and mistakes

Every stored object is a base relation. Materialized views also store data, but their contents are derived.
A base table is automatically the business source of truth. Ownership, provenance and governance determine that role.
A view is always an independent copy. An ordinary view is normally a stored query definition, while materialization is a separate choice.

Business use

The base-relation distinction supports data lineage, change impact analysis and reliable reporting. It helps teams determine where a correction belongs and which downstream results may need to be recomputed.

When a metric looks wrong, identify its contributing base tables, filters, joins and refresh times. Correcting a spreadsheet cell may conceal the symptom without fixing the stored fact or calculation that produced it. Conversely, editing an authoritative record to force one report to match can introduce a wider error.

For AI assistants that query databases, provide object descriptions that distinguish base tables, ordinary views and refreshed summaries. Include ownership and freshness information. A model should not infer that two table-like names represent equally current or equally authoritative data simply because both can be queried.

Check your understanding

A database stores the result of a query counting orders by month and refreshes it nightly. Is it necessarily base data because the result occupies storage?

Answer: No. A stored derived result, such as a materialized view, remains derived. Its definition and maintenance process matter more than the fact that it is stored.

Related terms

Relation
View
Materialized view
base table
query
data lineage
transaction visibility
source of truth

Learn next

Read views and materialized views. Trace one dashboard number to its stored input tables, noting every transformation and refresh boundary before deciding where an error should be corrected.

Sources

PostgreSQL, information_schema.tables: https://www.postgresql.org/docs/current/infoschema-tables.html. Primary documentation distinguishing table types.

PostgreSQL, CREATE VIEW: https://www.postgresql.org/docs/current/sql-createview.html. Primary documentation of query-defined views and updatability.

PostgreSQL, Materialized Views: https://www.postgresql.org/docs/16/rules-materializedviews.html. Primary explanation of stored derived results. The order and membership cases are original illustrations.

Previous: Base rate Next: Baseline