Let’s Talk About Database Schema

Previous post on this theme, “Designing a Relational Database – Data Modeling“.

Seriously, let’s talk about schema in the abstract and the literal implemented schema in some of the most popular databases.

What is a schema?

In general, outside the specific realm of relational databases, a “schema” is a conceptual framework or blueprint that defines the structure, relationships, and constraints of data or information. It provides a way to describe and organize data in a structured manner. This concept of schema is not unique to databases; for instance, in GraphQL, a schema defines the types, queries, mutations, and the relationships between them, outlining the set of possible operations that can be executed against the API and the shape of the data that is returned.

The Oddity of Different Implementations in Databases

From the viewpoint of someone familiar with the general idea of a schema, it can indeed seem unusual that databases like SQL Server, Oracle, MariaDB/MySQL, and PostgreSQL each interpret and implement schemas in slightly (or sometimes, vastly) different ways. While the core idea behind a schema as a structured container or namespace for database objects remains somewhat consistent, the exact nature, utility, and behavior of schemas vary across these systems.

Why Each Database Implements Schema Differently

1. Historical and Legacy Reasons: Many database systems started their journey decades ago. Over time, as they evolved, they built upon their existing architectures, leading to variations in features like schema. For instance, the idea of a schema in Oracle being closely tied to a user comes from Oracle’s early architectural decisions.
2. Design Philosophy and Target Audience: Some databases were designed with specific audiences in mind. Oracle, for example, was designed for enterprise-level applications, which might have influenced their user-centric schema design. On the other hand, MySQL, initially envisioned for web applications, treats schemas synonymously with databases, perhaps for simplicity.
3. Standards Compliance vs. Practicality: While there are ANSI SQL standards that databases might strive to adhere to, there’s also a balance to strike between standards compliance and offering features that are deemed more practical or beneficial for the database’s primary users. For instance, PostgreSQL, which aims to be highly standards-compliant, also introduces advanced features not in the standard when it sees fit.
4. Competitive Differentiation: Sometimes, databases introduce or tweak features to differentiate themselves in the market. These variations can sometimes lead to differences in core concepts like schemas.
5. Community and Governance Influence: Open-source databases like PostgreSQL and MariaDB can be influenced by their developer communities. Different communities might prioritize certain features or philosophies, leading to divergent implementations.
6. Flexibility and Extensibility Concerns: Databases might implement schemas in ways they believe are more flexible or extensible for future changes. This might lead them to adopt non-standard or unique approaches.

In essence, the varying implementation of schemas in different databases is a result of a blend of historical decisions, design philosophies, target audiences, and practical considerations. It’s a testament to the evolving nature of database systems and the diverse needs they aim to address.

Looking @ Relational Database Schema

A schema in the context of a relational database can be understood as a structured collection or container of database objects which can include tables, views, indexes, procedures, and more. It can be used to logically group these objects, often for reasons such as security, manageability, or multi-tenant considerations. In many databases, a schema also represents a namespace, meaning that objects within the schema must have unique names, but the same object name can exist in different schemas.

Here’s a brief overview of schemas in the databases you mentioned:

1. SQL Server:
   • Definition: In SQL Server, a schema is a container of objects and can be assigned to specific users or roles for security purposes. By default, there’s a schema called “dbo” (database owner).
   • Creation:

CREATE SCHEMA SchemaName AUTHORIZATION OwnerName;

2. Oracle:
   • Definition: In Oracle, the concept of schema is closely tied with the user. When you create a user, you’re essentially creating a schema. The user’s schema contains all the objects owned by that user.
   • Creation: You don’t create a schema directly in Oracle; instead, you create a user, and the schema gets created automatically.

CREATE USER username IDENTIFIED BY password DEFAULT TABLESPACE tablespace_name TEMPORARY TABLESPACE temp_tablespace_name;

3. MariaDB/MySQL:
   • Definition: The term “schema” is synonymous with “database” in MySQL/MariaDB. It’s essentially a collection of tables, views, and other database objects.
   • Creation:

CREATE DATABASE SchemaName;

4. PostgreSQL:
   • Definition: PostgreSQL uses schemas in a way that’s more in line with the standard relational database definition. A PostgreSQL database can contain multiple schemas, and each schema can have its own set of tables, views, indexes, etc.
   • Creation:

CREATE SCHEMA SchemaName;

Creating a Schema with ANSI SQL: The American National Standards Institute (ANSI) sets the standard for SQL, and the basic syntax for creating a schema in ANSI SQL is:

CREATE SCHEMA SchemaName;

However, it’s crucial to understand that actual implementations might vary between database systems, and not all databases will support the full ANSI standard. Always refer to the documentation specific to the database you’re working with.

SQL Server Schema

In SQL Server, the concept of “schema” is multi-faceted, and understanding its utilization can be broken down through the lenses of the “how,” “what,” “when,” and “where.”

How are schemas used in SQL Server?

1. Object Organization: Schemas are used to logically group and manage database objects like tables, views, stored procedures, etc. For example, a table can be created within a schema as follows: CREATE TABLE SchemaName.TableName (Column1 DataType1, Column2 DataType2, ...);
2. Namespace Management: Schemas provide namespaces, which allow objects of the same name to coexist in different schemas within the same database.
3. Permission Management: Schemas enable finer control over permissions. You can grant or revoke permissions on a schema, thereby affecting all the objects within that schema.

What objects can be associated with a schema in SQL Server?

Almost all types of database objects can be associated with a schema, including:

• Tables
• Views
• Stored procedures
• Functions
• Types
• Triggers
• Indexes
• And more…

When are schemas used in SQL Server?

1. Design Time: Schemas are often considered during the initial database design phase. A well-structured schema strategy can make future modifications and scaling easier.
2. Migration: When migrating or integrating data from other systems, schemas can be used to segregate different sets of data.
3. Permission Setting: When there’s a need to establish or modify permissions on a set of related database objects.
4. Refactoring: To reorganize objects for better clarity or performance.

Where are schemas found in SQL Server?

Within a SQL Server database, you can find schemas listed in the Object Explorer under the specific database node. They reside at the same hierarchical level as Tables, Views, etc., but they act as a container or namespace for these objects.

Purpose of Schemas in SQL Server:

1. Logical Organization: Schemas provide a way to logically group related database objects, making the database more understandable and manageable.
2. Security and Permission Management: By controlling access to a schema, administrators can indirectly control access to all objects within that schema.
3. Flexibility: Schemas offer flexibility in database design and administration. For instance, you can transfer ownership of a schema (and its objects) from one user to another.
4. Namespace Management: Avoid conflicts by having objects of the same name in different schemas.
5. Migration and Integration: Facilitate smoother data migration or integration from other systems by grouping imported objects under specific schemas.

In SQL Server, schemas play a pivotal role in enhancing organization, security, flexibility, and management of database objects. They act as both organizational containers and security boundaries, making them essential in a variety of database operations and strategies.

Oracle Schema

The concept in Oracle of a “schema” intertwines closely with that of a “user”. A schema in Oracle is effectively the collection of database objects that are owned by a user. This design differs from some other RDBMS systems like SQL Server, where a schema is more of a logical container or namespace.

How are schemas used in Oracle?

1. Ownership of Objects: Each database object belongs to a schema, and the schema name is, in essence, the username of the account that owns it.
2. Namespace Management: Schemas differentiate objects of the same name but owned by different users.
3. Security and Permissions: By granting or revoking privileges on a specific user (schema), you can control access to the objects within that schema.

What objects can be associated with a schema in Oracle?

Almost all database objects can belong to a schema in Oracle:

• Tables
• Views
• Sequences
• Stored procedures and functions
• Packages
• Indexes
• Synonyms
• Triggers
• And more…

When are schemas used in Oracle?

1. Database Creation and Design: Schemas are implicit when users are created and are usually mapped based on roles and responsibilities in the application.
2. Managing Permissions: Granting or revoking access to sets of related database objects is done by adjusting user privileges.
3. Organizational Segregation: Different departments or functionalities in an organization might get separate schemas for better organization and control.
4. Application Deployment: Different applications or modules might utilize separate schemas.

Where are schemas found in Oracle?

In Oracle, the idea of a schema is closely tied with users. So, when you’re looking at user accounts in an Oracle database (typically through tools like Oracle SQL Developer, or through the Oracle Enterprise Manager), you’re essentially looking at the list of schemas. Each user’s objects will reside within their schema.

Purpose of Schemas in Oracle:

1. Ownership and Organization: Each schema is a clear owner of its set of database objects, which ensures proper organization and management.
2. Security: Database security is implemented at the schema (user) level. Permissions granted or revoked on a schema affect all objects under that schema.
3. Namespace Management: Facilitates the coexistence of objects with the same name, owned by different schemas.
4. Logical Separation: Provides logical separation of database objects, which can be useful for managing large applications or multiple applications within the same database.
5. Resource Management: Oracle allows for resource management policies at the user/schema level, so certain schemas can be allocated more or fewer resources based on their requirements.

Oracle schemas (users) are foundational to the design and organization of the database. They facilitate ownership, security, logical organization, and management of database objects. This integrated user-schema approach is a distinctive aspect of Oracle’s database design.

MariaDB/MySQL Schema

In MariaDB and MySQL, the term “schema” is synonymous with “database”. The words “schema” and “database” are used interchangeably, and while in many systems a schema is a logical container inside a database, in MariaDB/MySQL, they are essentially the same thing.

How are schemas (databases) used in MariaDB/MySQL?

1. Container for Objects: A schema (or database) in MariaDB/MySQL acts as the primary container for tables, views, stored procedures, and functions.
2. Namespace Management: Each schema acts as a namespace, ensuring that objects like tables have unique names within that schema but can have the same name in different schemas.
3. Security: Privileges can be granted or revoked at the schema level, affecting access to all objects within that schema.

What objects can be associated with a schema (database) in MariaDB/MySQL?

A schema in MariaDB/MySQL can contain:

• Tables
• Views
• Stored procedures and functions
• Triggers
• Events
• And other relational objects

When are schemas (databases) used in MariaDB/MySQL?

1. Database Creation: When setting up a new application or service, a new schema might be created to keep its data logically separate.
2. Data Segregation: For multi-tenant applications, separate schemas might be used for each tenant or group of tenants.
3. Backup and Restoration: Operations might be performed at the schema level to backup or restore data for specific applications or services.
4. Application Deployment: Different applications might utilize separate schemas for better management and isolation.

Where are schemas (databases) found in MariaDB/MySQL?

You can view the list of schemas using client tools like MySQL Workbench, phpMyAdmin, or even the command-line interface. In the command-line client, the command SHOW DATABASES; lists all the schemas.

Purpose of Schemas (Databases) in MariaDB/MySQL:

1. Logical Separation: Schemas provide logical separation of data, which is essential for organizing data, especially in environments with multiple applications or services.
2. Security and Permission Management: Provides a level at which permissions can be managed, allowing for control over who can access or modify data.
3. Backup and Maintenance: Facilitates operations at the schema level, making tasks like backup, restoration, or optimization more manageable.
4. Namespace Management: Ensures that table names and other objects can be unique within a schema, allowing for objects with the same name in different schemas.
5. Flexibility: Offers flexibility in managing resources, performance settings, and configurations at the schema level.

MariaDB/MySQL schemas (or databases) play a central role in data organization, security, and management. Their purpose is to provide a logical structure, ensuring organized, secure, and efficient data handling.

PostgreSQL Schema

In PostgreSQL, the concept of “schema” has a distinctive role. Unlike MariaDB/MySQL, where a schema is synonymous with a database, in PostgreSQL, a schema is a namespace within a database.

How are schemas used in PostgreSQL?

1. Namespace for Objects: A schema provides a namespace, making it possible for multiple objects (such as tables or functions) to have the same name as long as they are in separate schemas.
2. Search Path: PostgreSQL uses a “search path” to determine which schema to look into when referencing an object. The search path can be set so that you don’t have to qualify the schema’s name when accessing its objects.
3. Security: Access permissions are defined at the schema level. You can control which roles have access to which schema and its contained objects.

What objects can be associated with a schema in PostgreSQL?

Within a schema in PostgreSQL, you can have:

• Tables
• Views
• Indexes
• Sequences
• Data types
• Functions
• Operators
• Domains
• And other database objects

When are schemas used in PostgreSQL?

1. Initial Database Design: During the database’s design phase, schemas can be created to separate functionalities or modules.
2. Data Segregation: For multi-tenant applications, separate schemas might be created for each tenant.
3. Module Isolation: Different application modules might have their own schemas for better organization.
4. Migrations: When merging databases or tables, schemas can be helpful in avoiding name collisions.

Where are schemas found in PostgreSQL?

In PostgreSQL, schemas are found within a specific database. You can view them using tools like pgAdmin, DBeaver, or the command-line interface (psql). Within psql, the command \dn will list the schemas.

Purpose of Schemas in PostgreSQL:

1. Namespace Management: Allows for the coexistence of database objects with the same name in the same database, as long as they reside in different schemas.
2. Logical Organization: Facilitates the logical grouping of database objects based on functionality, application modules, or other criteria.
3. Security: Provides a layer for access control. By setting permissions at the schema level, you can control access to sets of objects.
4. Flexibility: Schemas offer flexibility in managing and organizing objects. You can have multiple schemas in one database, reducing the need to create separate databases.
5. Efficient Management: Eases tasks such as backup, restore, or migration at the schema level, offering granularity in database operations.

PostgreSQL schemas play a crucial role in offering a logical organization within a database, providing namespaces, and enhancing security and management granularity. This approach gives PostgreSQL databases a structured yet flexible architecture, accommodating diverse and complex data organization needs.

Schema Summary

In relational database systems, schemas serve as blueprints that define the structure, relationships, and constraints of data. SQL Server uses schemas as logical containers or namespaces within a database, facilitating the organization, security, and management of database objects. Oracle uniquely intertwines the concept of a “schema” with a “user”, where a schema effectively represents the collection of database objects owned by a user account. This design emphasizes both ownership and organization of objects, with the schema acting as the principal entity for object management and permissions.

Conversely, in MariaDB and MySQL, a “schema” is synonymous with a “database”, serving as the primary container for tables, views, and other related objects. This definition focuses on logical data separation and namespace management within the database server. PostgreSQL stands out with its nuanced approach, where a schema functions as a namespace within a specific database, allowing for object coexistence with identical names, as long as they reside in separate schemas. This design in PostgreSQL fosters intricate organizational capabilities and enhances security and management granularity within a single database.