The ANSI/SPARC report defines a three-schema architecture for database systems: external, conceptual and internal schemas. What does each level describe, and what is the architecture trying to achieve?

## The three levels The ANSI/SPARC Study Group on Data Base Management Systems proposed in 1975 that a database be described at three separate levels, so that decisions at one level do not force changes at another. **Internal schema (physical level).** How the data is actually stored: file and page organisation, row format, which indexes exist and of what kind, clustering, partitioning, compression, and where things live on disk. It answers 'how is this laid out and reached'. There is exactly one internal schema per database, and it is the DBA's and engine's territory. **Conceptual schema (logical level).** The complete logical description of the enterprise's data: entities, their attributes and data types, relationships between them, and integrity constraints — all expressed with no reference to storage. It answers 'what data exists and what rules hold'. There is one conceptual schema, and it is the shared contract every application implicitly agrees to. In SQL terms, the tables, columns, keys and constraints. **External schemas (view level).** Each user community gets its own description of the slice it cares about, in the shape most convenient for it. A payroll application sees employees with salary; a directory application sees the same employees without it, perhaps with department name folded in from another entity. There are many external schemas, and they may restrict rows, restrict columns, rename, join, or derive. ## The two mappings The levels only pay off because of the mappings between them. - The **conceptual-to-internal mapping** says how logical structures are realised in storage: which table lives in which physical structure, which columns are indexed, how a logical row becomes bytes. - The **external-to-conceptual mapping** says how each user view is derived from the conceptual schema — in SQL, essentially a view definition. When a change happens at one level, the adjacent mapping is supposed to absorb it. ## What the architecture buys **Physical data independence:** change the internal schema — add or drop an index, reorganise a table, change compression or partitioning — and only the conceptual-to-internal mapping changes. The conceptual schema and every application query stay as they are. This is why you can tune a production database without a code release, and it is the strongest guarantee real engines actually deliver. **Logical data independence:** change the conceptual schema — add a column, split one entity into two, rename something — and if the external-to-conceptual mapping can be rewritten to reproduce the old external schema, applications continue to run unchanged. This is weaker in practice, because some logical changes genuinely alter what the view can present. **Sharing and security as side effects:** because each group has its own external schema, one database serves many applications with different shapes and different visibility, and a view can be the unit of access control. ## Mapping it onto a real SQL database - Internal ≈ tablespaces, heaps and index structures, page and row layout, partitioning, storage parameters, and engine statistics. - Conceptual ≈ base tables, columns, primary and foreign keys, check constraints — the schema in the migration files. - External ≈ views, and by extension the API layer or ORM projections applications actually query. The abstraction is not clean in reality: query performance leaks physical information upward, because whether a query is fast depends on the internal schema even though the query text does not mention it. That is the standard critique — the levels are independent for *correctness* but not for *performance*. ## Why this is still worth knowing The architecture is the vocabulary behind everyday decisions. 'Which level does this change live at?' tells you the blast radius: an internal-level change is an operations task, a conceptual-level change is a coordinated migration across every consumer, an external-level change affects one application. Teams that never make the distinction end up with applications that depend on index names, physical order, or a table's exact shape — the dependencies the three levels were invented to prevent.