What is a Database Schema?

The term “database schema” has a few different meanings.

Let’s look further into these definitions.

What is a Database Schema?

A database schema can be a visual representation of a database, a set of objects that belong to a user, and a set of rules that specify the allowed data.

All of these definitions are valid.

A database schema is a definition that describes the configuration of the database. It is often represented by a diagram that represents the tables and relationships in the database. The database schema includes:

• Tables
• Views
• Relationships (including primary keys and foreign keys)
• Stored procedures
• Views

 

Database Schema Example

Here’s an example of a database schema.

This diagram or schema includes a table for a website user, the posts they have made, and logins to a website. A database schema could be a simple as this, or it could be much more complicated with procedures, views, and many more tables.

 

Schema vs Database

Is a schema the same as a database? Well, it depends on the vendor – which makes it more confusing!

 

Oracle Schema

In Oracle, a schema is a collection of data and objects, and is owned by a user. So, it’s not quite the same as a database, as a database can have many schemas and users.

A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include the following types of objects…

(source)

 

SQL Server Schema

In SQL Server:

A schema in a SQL database is a collection of logical structures of data. The schema is owned by a database user and has the same name as the database user. From SQL Server 2005, a schema is an independent entity (container of objects) different from the user who creates that object.

(source)

So, in SQL Server, a schema is different to a database and is also separate from a user.

 

MySQL Schema

In MySQL:

Conceptually, a schema is a set of interrelated database objects, such as tables, table columns, data types of the columns, indexes, foreign keys, and so on.

In MySQL, physically, a schema is synonymous with a database. You can substitute the keyword SCHEMA instead of DATABASE in MySQL SQL syntax, for example using CREATE SCHEMA instead of CREATE DATABASE.

(source)

So, in MySQL, a schema is the same thing as a database. You can use either Create Database or Create Schema when creating a database in MySQL.

 

PostgreSQL

In PostgreSQL:

A database contains one or more named schemas, which in turn contain tables. Schemas also contain other kinds of named objects, including data types, functions, and operators. The same object name can be used in different schemas without conflict; for example, both schema1 and myschema can contain tables named mytable. Unlike databases, schemas are not rigidly separated: a user can access objects in any of the schemas in the database he is connected to, if he has privileges to do so.

(source)

So it seems that PostgreSQL works in a similar way to Oracle and SQL Server in that it has a separate concept for schemas that contain objects.

 

How to Create a Schema Diagram

Creating a schema could mean either creating a diagram, or creating a schema in SQL.

If you want to create a schema diagram, this is also known as an Entity Relationship Diagram or ERD. Read this post on what this is and how to create one.

 

How to Create a Schema in SQL

If you want to create a schema in SQL, the method to do it depends on the vendor.

Oracle

In Oracle, a schema is automatically created when you create a user, so CREATE SCHEMA doesn’t do anything.

To create a user:

CREATE USER yourusername;

 

SQL Server

In SQL Server you can create a schema using this command:

CREATE SCHEMA yourschemaname;

You can then add users and objects to it.

MySQL

In MySQL, because a schema is synonymous with a database, the CREATE SCHEMA command will create a database. There is no way to create a separate schema.

 

PostgreSQL

In PostgreSQL, you can create a schema with this command:

CREATE SCHEMA yourschemaname;

This will create a new schema for you to use.

 

If you’re interested in creating a database, rather than a schema, read this guide.

Summary

A schema is a definition that describes the database. This can be a diagram that shows different aspects of the database or an area in the database for storing objects.