What Is Three-Schema Architecture?

If you're a database designer, you know there's more to the world than just a bunch of tables. Sure, there are tables for customers and products, but what's the point of having those things if you can't tell them apart? It's time for a three-schema architecture. The three-schema architecture is an idea in relational database design that breaks a database into three categories according to its use and structure and the roles played by system administrators, designers, and end users. The first is the conceptual schema, which describes how the business application uses data. It is usually relatively static, so it only changes a little. The second schema is called the logical schema; this contains all of the tables in the database. It's also called physical because it represents how data is stored on a disk or tape. Finally, there's a third schema called the external schema; it consists of views or projections created from other tables or queries so they can be presented to users without exposing sensitive information such as passwords or credit card numbers. Each one of these schemas has its own set of rules for creating columns and constraints on them so that they can be accessed correctly by each role type: end users need only see their data, whereas system administrators will need access to everything. At the same time, designers will want to make changes without affecting existing applications until they're ready for deployment. The three-schema architecture is a way to look at the different maintenance levels. The conceptual level contains items dealing with database tables and queries, while the physical story deals with issues like memory handling. It's also known as data independence because it allows changes to be made on one level without affecting another. So if you make any changes to your database tables, they can be done without affecting the physical or logical schema.