Pronounced skee-ma, schema refers to a formalized, consistent structure of organizing information

In computing, the term schema refers to the diagrammatic representation or structured framework of a database used to interpret information or different types of data. It is the blueprint that provides the details of how a database is constructed and its relationship with other databases.

is the structure of a database system, described in a formal language supported by the database management system (DBMS). In a relational database, the schema defines the tables, the fields in each table, and the relationships between fields and tables.

Schemas are generally stored in a data dictionary. Although a schema is defined in text database language, the term is often used to refer to a graphical depiction of the database structure.

Types of schemas

There are three types of schemas based on their representation: Physical Schema, Logical Schema, and View Schema.

Physical Schemas

A physical schema, also known as an internal schema, is a representation of a data design that describes how data is to be constructed and stored physically in the database. At this level, data is represented as files, indices, tables, clusters, etc. A physical schema is used to evaluate storage estimates.

Being the lowest level in the database, it’s easy to make changes to a physical schema, like creating a new file or index and storing new files in the storage system. At this level, database administrators (DBAs) determine the data’s method of storage and storage structure.

For example, if a database administrator wants to represent the physical schemas of students and their projects, they can create two files: Datafile1 (Students) and Datafile2 (Projects).

Logical Schemas

A logical schema, or conceptual schema, is the design of the database that describes the structure of different types of data as fields, entities, attributes, and relationships along with their data types. It’s mainly used to create relationships between tables or data. However, the structure of data storage is hidden, meaning only database administrators and developers can work at this level.  An example of logical schemas for students and project details might include:


View Schemas

A view schema, or external schema, is the view-level structure of the database, and it defines how users interact with the database. View schemas provide different views of data for different groups of users.

For example, a user can see either the details of each student, their names and IDs, and their projects or the details of a particular student. The users may be teachers, students, and so on.

Importance of database schemas

Schemas help the database understand what the data is, and how a DBA or programmer can work with this data. It also allows the database engine to recognize the data patterns and respond to the user queries with the right information. A good schema always tries to make the information visible to the users.

In relational databases, schemas provide more priority to data consistency and the structures that are essential to understanding the relationships between the data. While in the non-relational databases, schemas help to achieve high performance by aligning the data in patterns which is easily accessible.

Who uses schemas?

In most companies, database administrators develop schemas to help the database management team or database users easily access the information. They use schemas to organize and align a company’s data securely. DBAs usually determine the method and structure for data storage.

Static Schemas vs. Dynamic Schemas

There are two basic types of databases: relational and non-relational. To understand the difference between them, it’s essential to know the difference between static and dynamic schemas.

Relational databases are mainly associated with static schemas, in which the data patterns are pre-defined, and the structure of the data cannot be changed. It can be configured only when creating a workspace. For example, textbooks use a static schema approach.

Dynamic schemas are more prevalent with non-relational databases, and they can be configured when adding additional data to the system. For example, a user can create and add categories to a customer table, and the schemas of the database change when the user adds more data.

Database Schemas vs. Database Instance

In a database management system, a schema is a relatively static view of a database that describes its overall structure. Comparatively, an instance is a dynamic collection of data that is stored in a database at specific moments.

This definition was updated in January 2022 by Siji Roy.

Vangie Beal
Vangie Beal
Vangie Beal is a freelance business and technology writer covering Internet technologies and online business since the late '90s.

Top Articles

List of Windows Operating System Versions & History [In Order]

The Windows operating system (Windows OS) refers to a family of operating systems developed by Microsoft Corporation. We look at the history of Windows...

How to Create a Website Shortcut on Your Desktop

Website Shortcut on Your Desktop reviewed by Web Webster   This Webopedia guide will show you how to create a website shortcut on your desktop using...

What are the Five Generations of Computers? (1st to 5th)

Reviewed by Web Webster Each generation of computer has brought significant advances in speed and power to computing tasks. Learn about each of the...

Hotmail [Outlook] Email Accounts

Launched in 1996, Hotmail was one of the first public webmail services that could be accessed from any web browser. At its peak in...

AutoLocky Ransomware

AutoLocky is ransomware written in the popular AutoIt scripting language. It uses strong...

Data Governance

Data governance is a term used to refer to the management of processes,...

Capacity Planning

Capacity planning is a process that helps organizations determine the resources needed to...