What is Database Schema

A few meanings

Database schema is one of those fuzzy terms with a couple of meansings and interpretations. Depending on context it can mean:

  1. Data model in database
  2. Whole of data structures and programs within a database
  3. Namespace (element of SQL)

1. Data model

One of the meanings of database schema is the arrangement of tables in the database - specific tables, data types, primary and unique keys and foreign key constraints. It tied with specific DBMS and defined in formal language.

2. Database implementation

In broader sense database schema is the entirety of database elements - tables, constraints, views, stored procedures, data types etc.

3. Namespace (element of SQL)

Another meaning of schema is schema is a specific element in most relational database engines used for grouping objects. You can think of schema as a namespace or container that holds tables, views, functions etc. Their names can duplicate across different schemas.

You create schema using following syntax in most databases:

CREATE SCHEMA HumanResources;

This creates new schema and it is prefix of each object within that schema:

Why use schemas

You may want to split database into schemas for follwing reasons:

  • Easy to assign permissions to entire schema,
  • Use same name for two or more different tables (or other object),
  • Group similar objects to reduce complexity.

Support in RDBMS

Virtually all relational databases support schemas, it's part of the SQL standard. But databases differ in function of schemas and whether they support "database" level of hierarchy.

DBMS Database/Schema Implicit schema
Just schemas
Oracle Server => Schema = User n/a
MySQL Server => Database = Schema n/a
MariaDB Server => Database = Schema n/a
Teradata Server => Database = Schema n/a
Firebird Server => Database = Schema n/a
Interbase Server => Database = Schema n/a
SQLite File = Database = Schema main
Databases and schemas
SQL Server Database => Schema dbo
PostgreSQL Database => Schema public
Azure SQL Database Database => Schema dbo
IBM Db2 Database => Schema n/a
Amazon Redshift Database => Schema public
Snowflake Warehouse => Database => Schema public
SAP/Sybase ASE Database => Schema dbo
SAP HANA Database => Schema n/a
Vertica Database => Schema public
IBM Informix Database => Schema n/a
Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.