What Is a Database Schema?

A schema is the blueprint of a database that ensures optimal organization

Database schema layout

A database schema is a collection of metadata that describes the relationships between objects and information in a database. An easy way to envision a schema is to think of it as a box that holds tables, stored procedures, views, and related data assets. A schema defines the infrastructure of this box.

Database Schema Definition

At its most basic level, the schema serves as a container for data assets. However, different database vendors operationalize schemas in different ways. Oracle, for example, treats every schema as a user account. To create a new schema, a database administrator creates a new database user with the intended schema name.

A schema is not the same as a data model.

Why Schemas Matter

Because schemas constitute a basic structural feature of a database, most database environments apply access permissions to objects on a schema level.

For example, a company database might contain a series of users. Each user incurs a schema, but access to different schemas is granted individually, and with the granularity of permissions, to users outside of the home schema.

Most database management tools don't list schemas; instead, they list databases and users.

Screenshot of php MyAdmin showing how to edit privileges

For example, a company creates user accounts (schemas) for Bob and Jane. It also creates accounts for departments like HR and marketing. Then, it gives an analyst in each department access to the department's schema account.

The HR analyst creates tables and views within the HR schema and grants access to Bob to read (but not write to) a table that lists employee names and employee ID numbers. Also, the HR analyst may grant access to Jane to read and write to a table that lists employee phone numbers.

By granting access this way, only the right roles and users can read, write, or modify the data in a self-contained data asset within the larger database.

Every database engine looks to schemas as the foundational method of segregating data in a multi-user environment.

Different database engines treat users and schemas differently. Refer to the documentation specific to your database engine to discover the specific syntax and logic models surrounding users, schemas, and permissions grants.

Creating Schemas

A schema is formally defined using ​Structured Query Language. For example, in Oracle, you create a schema by creating the user account that owns it:

CREATE USER bob
IDENTIFIED BY temporary_password
DEFAULT TABLESPACE example
QUOTA 10M ON example
TEMPORARY TABLESPACE temp
QUOTA 5M ON system
PROFILE app_user
PASSWORD EXPIRE;

Other users are granted access to new schemas by virtue of their username or by one or more roles that the user account has been added to.

Schemas vs. Data Models

A schema isn't intrinsically structured to do anything; instead, it's an infrastructure to support permissions segmentation in a database. The terms schema and data model are used interchangeably, however.

It's confusing, but not wrong, to use schema to reference a specific collection of tables and views joined by specific relationships among them. The schema-as-architecture approach outlined in this article is the capital-S version of schema. The schema-as-data-relationships is the lowercase-S version — a synonym. Many online reference sites blur this essential distinction.

A data model is a collection of tables and views joined on specific keys. These data assets, together, serve a business purpose. It's perfectly acceptable to apply a data model to a schema — in fact, for large and complex data models, associating them with schemas makes for smart database administration. But it's not logically necessary to use a schema for a data model or to treat a data model as a schema.

Database Schema for a store

For example, the HR department might include in its schema a data model for employee performance reviews. Instead of creating a schema for these reviews, the data model can sit in the HR schema (along with other data models) and remain logically distinct through prefixes of the table and view names for the objects in the data model.

The data model might earn an informal name, such as performance reviews, and then all tables and views might be prefixed by pr_. The employee listing table might be referenced as hr.pr_employee without requiring a new schema for the performance reviews.