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

Continue reading “Let’s Talk About Database Schema”