Three-Schema Architecture

Databases are characterized by a three-schema architecture because there are three different ways to look at them, each important to a different group in an organization. This lesson covers all three levels, how they fit together, and why separating them this way is one of the most consequential decisions in the design of relational database systems.

1. The external schema (user views) is where database users and application developers interact with the data.
2. The conceptual schema (logical schema) is where data modelers and database designers work.
3. The internal schema (physical schema) is where DBAs and database engineers work, though in practice these roles can overlap, especially on smaller teams.

The three-schema architecture is a framework used in database management systems to separate user applications from the physical database. This separation ensures data independence, improves flexibility, and simplifies database design and maintenance. The three levels map directly onto the user view, logical schema, and physical schema terminology used elsewhere in this course.

This level describes the part of the database relevant to a particular user or group of users. Different users may have different views of the same underlying data, depending on their needs. A Sales view might expose customer and order totals, a Support view might expose open tickets tied to a customer, and a Reporting view might expose aggregated figures across both, three different windows onto overlapping data, each shaped around what that group actually needs. The external level focuses on user-specific requirements, hides irrelevant or sensitive data, and gives each group an interface tailored to its own purpose.

This level describes what data is stored in the database and the relationships between it: a single, organization-wide view, independent of any specific user or application. It is what the Sales view, Support view, and Reporting view are each built from: one shared set of entities, relationships, and constraints, Customer, Order, Ticket, and so on, rather than three separately maintained copies. The conceptual level acts as a bridge between the external and internal levels, keeps data consistent across the whole database, and hides physical storage details from users and applications.

This level describes how data is physically stored: storage structures, file organization, indexing, and access methods, including details such as datatypes, compression, and encryption. In practice this means files, pages, and indexes, the actual on-disk structures the conceptual schema's tables ultimately map onto. It determines, for instance, whether a table uses ordinary heap-organized storage or a more specialized structure such as an index-organized table, and which columns get indexes built on them. The internal level focuses on performance and storage efficiency, hides physical detail from the levels above it, and is primarily the concern of database administrators and system developers.

It would be simpler, on paper, to let each application talk directly to the raw storage: no mapping layers, no intermediate schema, just Sales querying whatever table structure the database happens to use today. That simplicity does not survive contact with a second application. The moment Support and Reporting also need customer data, each one either duplicates its own copy of that structure, or all three become permanently coupled to the exact same physical layout, unable to change independently of one another.

The three-schema architecture exists specifically to break that coupling. By inserting a conceptual schema between the external views and the internal storage, and by requiring every connection between levels to go through an explicit mapping, the architecture turns what would otherwise be a tangle of direct dependencies into a clean, layered structure. Each of the three groups, end users and application developers, data modelers and designers, DBAs and database engineers, can do their own work and make their own changes without having to coordinate every detail with the other two.

Data independence. This is the architecture's central payoff, and it is easiest to see rather than just read about.

Three separate views sit at the top, Sales, Support, Reporting, each mapped down to the same single conceptual schema beneath them, which is in turn mapped down to one internal schema and one stored database. Because each level only connects to its neighbor through a mapping, a change at one level does not have to ripple through the others. This is what logical and physical data independence actually mean in practice, not an abstract promise but a direct consequence of this funnel shape:

• Logical data independence. Add a new entity to the conceptual schema, a Shipment table, say, and the existing Sales, Support, and Reporting views keep working untouched, since none of them were defined against that new entity in the first place.
• Physical data independence. Rebuild an index, move a table to new storage, or change file organization at the internal level, and neither the conceptual schema nor any of the three views above it needs to change at all.

Modularity. Each level can be modified independently, which makes the system more flexible and easier to maintain.

Security. Users only see the data they are authorized to access, as defined by their own view; the Support team's view need not expose anything Sales-specific, and vice versa.

Simplified design. Separating user views, logical structure, and physical storage into distinct concerns makes the overall design process more manageable, and is a reference model underlying relational database systems broadly, including Oracle, MySQL, and SQL Server.

Example: in the Sales, Support, and Reporting views shown above, the external level is where each team sees only what it needs, the conceptual level defines the shared Customer, Order, and Ticket entities those three views all draw from, and the internal level specifies how those entities are actually stored, which files they live in, how they are paged, and which columns are indexed for fast lookup.

The three-schema architecture is a foundational concept in database design. By separating user views, logical schema, and physical schema, it ensures that a change at one level does not disrupt the others, which is what makes a database system robust and manageable as it grows.

Suppose the Reporting team asks for a new figure: average order value broken down by support ticket status, information that currently requires joining data the Sales and Support views were never designed to share directly. Because the architecture separates the three levels, this request can be handled without touching anything Sales or Support currently rely on.

The data modeler works at the conceptual level, confirming that Order and Ticket are already related through Customer in the shared schema, nothing new needs to be modeled, the relationship already exists; it simply has not been exposed to Reporting yet. A new Reporting view is then built directly against the existing conceptual schema, joining Order and Ticket the way the request requires. No change touches the Sales view, no change touches the Support view, and no change is needed at the internal level at all, since the underlying tables and their indexes already support the join efficiently. The request that looked, at first glance, like it might require restructuring the database turns out to need nothing more than a new mapping from an existing conceptual schema to a new external view, which is exactly the kind of change this architecture is built to absorb cleanly.

• User views. A user view is a saved query, created with SQL, that exposes a specific subset of the database to a particular user or group. Views are created using the data definition language (DDL), through a CREATE VIEW statement, and are then queried like any other table. A view on its own only defines what data is exposed, not who can see it; granting actual access to specific users or roles is a separate step, handled with SQL's access-control statements. The two together, a restricted view plus a grant, are what let a secretary see employee names and addresses for the company holiday card list, without also seeing salary data an employee database might contain.
• Data manipulation language (DML). A set of SQL commands used to manage and modify data within existing tables: adding, removing, or changing records.
• Logical schema. A database's overall logical plan, developed through ER diagrams that define table contents and how tables are linked together for data access. Database designers are responsible for creating the logical schema, though application developers and database administrators also rely on it. Logical design, the fourth of the six design stages in the database life cycle covered earlier in this module, is directly related to the logical schema; in fact, the logical schema is logical design's most significant end product.
• Physical schema and physical design. The physical schema describes how data is actually stored on the system that holds it. Physical storage is generally, though not always, handled by the RDBMS itself. Neither ordinary users nor database designers typically work at this level directly; it is the concern of database administrators and system developers, who interact with the RDBMS to manage storage structures, file organization, and access paths. Physical design, the fifth of the six DBLC design stages, is a related but distinct idea: it is the work of tuning a database for performance, which does involve the database designer, unlike the physical schema itself.

A short recap of this lesson:

• The three-schema architecture separates a database into external (user views), conceptual (logical schema), and internal (physical schema) levels, each the primary concern of a different group.
• Explicit mappings between adjacent levels are what deliver logical and physical data independence: a change at one level does not have to ripple through the others.
• Multiple external views, as different as Sales, Support, and Reporting, can all be built from one shared conceptual schema rather than each maintaining its own disconnected copy of the data.
• Logical design and physical design, two of the six database life cycle stages covered earlier in this module, are the work that produces the logical schema and tunes the physical schema respectively.

Before moving on to the next lesson, use the link below to read more about database three-schema architecture.
Three Schema Architecture
The next lesson introduces a special class of tools often used in database design.