What Is a Database Schema?

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.

Data Asset Containers

At its basic level, a schema serves as a container for data assets. However, different database vendors structure their 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.

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.

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 their 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) an HR table that lists employee names and employee ID numbers. Also, the HR analyst may grant access to Jane to read and write to an HR 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.

Creating Schemas

A schema is formally defined using ​Structured Query Language (SQL). 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

Like a data model, a schema isn't intrinsically structured to do anything. Instead, it's an infrastructure to support segmentation permissions in a database.

A data model is a collection of tables and views joined on specific keys. These data assets, together, serve a business purpose. It's acceptable to apply a data model to a schema—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.

For example, the HR department might include a data model for employee performance reviews in its schema. 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.