AI Glossary / Data engineering and architecture / Advanced

Three-schema approach

Definition

The three-schema approach separates a database's description into external, conceptual and internal levels. External schemas describe views needed by particular users or applications. The conceptual schema describes the shared logical meaning and structure of the database. The internal schema describes how data is represented and organized for storage. The separation supports reasoning about changes without unnecessarily exposing every application to storage details.

What it means

Different users can work with different views of the same underlying information. In a fictional learning platform, an instructor needs a class roster while an administrator needs enrollment totals. Both should use a consistent understanding of learner, offering and enrollment. Meanwhile, the database may store those records using structures neither user needs to understand. The approach makes these perspectives explicit. It is a framework for separating responsibilities, not a requirement to maintain three independent copies of the data or to place each level on a different computer.

How it works

Mappings connect the external views to the conceptual description and the conceptual description to the internal representation. The ANSI/X3/SPARC work, discussed in the 1985 DBMS reference-model report, provides the historical framework. Logical data independence concerns insulating external views from suitable conceptual changes; physical data independence concerns insulating the conceptual description from internal changes. Achieving either depends on the available mappings and implementation. The IRDS conceptual-schema report also places the conceptual level between external and internal descriptions. In practice, the framework helps a team identify which contract a proposed change affects and which dependent behavior must be checked.

Three practical examples

• An instructor-facing roster and an administration-facing count present different views derived from a shared enrollment model.
• A database team changes an access structure while checking that applications still receive the same logically defined records.
• A reporting team evaluates whether an existing external view can preserve its meaning when the underlying logical representation is reorganized.

Think of it this way

Imagine a library with a reader's catalog, an agreed description of books and copies, and a shelving plan. A reader searches the catalog without needing the shelving plan's storage details. Moving shelves should not silently redefine what counts as a book or an available copy. The analogy illustrates distinct descriptions and the connections between them, rather than three unrelated libraries.

Worked example

Consider an original fictional conceptual model with Learner, Offering and Enrollment. Three enrollment records connect learners L1 and L2 to offering O1 and learner L3 to offering O2. An external roster view for O1 returns L1 and L2, while an external summary view reports O1: 2 and O2: 1. Suppose the internal implementation adds an index on the offering identifier. Under the stated unchanged logical data and view definitions, both external results should remain the same. The index may change how records are located; it should not change these counts. Now suppose the business redefines the summary to include only active enrollments and marks L2 inactive. O1's count becomes one. That second change affects meaning, so it cannot be justified as merely a storage adjustment. The example distinguishes an internal access change from a deliberate change to the view's interpretation.

Illustrative case study

In a fictional AI reporting service, an assistant queries a documented enrollment view. Engineers reorganize storage to improve common queries. They compare the view's results before and after the change on a fixed dataset, including empty offerings and repeated enrollments permitted by the business rules. Later, a separate proposal changes which enrollment statuses are included. The team treats that as a change to the reporting contract and updates the assistant's description and examples. The scenario illustrates how layered descriptions help identify the meaning of a change. It does not imply that a view alone guarantees compatibility or that every schema migration can preserve every application unchanged.

Common misconceptions and mistakes

Three-schema architecture is not the same as a three-tier application split into presentation, application and database services. Its levels describe data from different perspectives. Another mistake is equating the conceptual schema with a specific physical table layout or assuming an external schema must be a graphical screen. An application-facing data view also belongs to the external perspective. Data independence is an objective with practical limits, not permission to change semantics unnoticed. Finally, providing a restricted view does not automatically establish complete access control if other routes still expose the underlying information.

Business use

For a proposed AI data service, describe the business entities first, then specify what the assistant is allowed and expected to see. Record which meanings are stable across storage changes and which changes require consumers to adapt. In the fictional enrollment service, reviewers can ask whether a release changes record access, the shared logical model or the reporting definition. That distinction helps assign the right validation work. Evaluate representative results and behavior across a migration rather than relying on an unchanged object name. The approach is most useful when its descriptions guide actual decisions and remain consistent with the implemented system.

People also ask

Does the approach require three databases?

No. The three levels are descriptions and perspectives, not necessarily separate data stores.

Is an external schema always a user-interface page?

No. It can describe an application's data view independently of its visual presentation.

Can every conceptual change be hidden from applications?

No. Some changes alter information or meaning that applications depend on, requiring coordinated updates.

Check your understanding

How many learners appear in the original O1 roster?

Answer: Two.

Should adding the internal index change that roster?

Answer: No, under the example's unchanged data and view definitions.

Why does excluding inactive enrollments change the count?

Answer: It changes the inclusion rule.

Which concept addresses insulation from internal storage changes?

Answer: Physical data independence.

Related terms

• External Schema
• Internal Schema
• Data independence
• conceptual schema
• database view
• schema mapping

Learn next

Sketch two user views over one small business model. Describe an internal change that should leave both views' meaning intact and a conceptual change that might affect them. For each change, write one result comparison and one question about compatibility. Explain why preserving a view's name alone would not establish unchanged behavior.

More in Data engineering and architecture

• Theta-join - A theta-join is a relational join that combines tuples from two relations when they satisfy a specified comparison condition, conventionally called theta.
• Third normal form (3NF) - Third normal form, or 3NF, is a relational database design condition defined through functional dependencies.
• Thread pool - A thread pool is a managed collection of worker threads that execute submitted tasks, usually reusing threads across multiple tasks.
• Three-tiered architecture - Three-tiered architecture is an application architecture that separates presentation, application logic, and data management into distinct tiers.
• Top-down design - Top-down design is a design approach that begins with broad goals and major concepts, then progressively refines them into more detailed structures and...
• Torus interconnect - A torus interconnect is a network topology in which nodes are arranged along multiple coordinate dimensions with connections that wrap around each dimension.

Sources

ANSI/X3/SPARC Database Architecture Framework Task Group, Reference Model for DBMS Standardization, NBSIR 85-3173, 1985: https://www.govinfo.gov/content/pkg/GOVPUB-C13-f55f11d78745e6c51f75a39a44913f22/pdf/GOVPUB-C13-f55f11d78745e6c51f75a39a44913f22.pdf IRDS Conceptual Schema report, hosted by John F. Sowa: https://www.jfsowa.com/ikl/irdscs.htm The enrollment model, library analogy and AI-reporting scenario are original educational illustrations. Historical reference material supports the architecture; no universal product-conformance claim is made.

Previous: Three-layer architecture Next: Three-tiered architecture