A database schema is the formal structure or definition that describes how a database's data is organised and constrained. In a relational database, a schema can encompass tables, columns, data types, primary keys, foreign keys, indexes, views, constraints, routines and other database objects, depending on the database system's terminology. The word schema can also have a more specific meaning in systems such as PostgreSQL, where a schema is a namespace containing database objects. In broader database design discussions, however, schema commonly means the overall structural definition of the database. This distinction is important because schema has both a conceptual meaning and a product-specific object meaning.
A schema defines the structure expected by applications and users. A relational schema might specify that a Customers table contains customer_id as an integer primary key, name as a text field and email as a unique value, while an Orders table contains order_id, customer_id, order_date and total_amount. It can also define that Orders.customer_id references Customers.customer_id. These definitions establish what information exists, what types of values can be stored and how different pieces of data relate to each other. The schema therefore acts as a formal contract between database structures and the software that uses them, although applications may impose additional rules that are not represented directly in the database.
Database schemas can be described at different abstraction levels. A conceptual schema represents major entities and relationships without necessarily specifying implementation details. A logical schema translates those concepts into structures such as relational tables, columns and constraints. A physical schema concerns how those logical structures are implemented in storage, indexes, partitions and other database-engine-specific mechanisms. Database theory and architecture often distinguish conceptual, logical and physical levels to separate user-facing data models from implementation details. The precise terminology varies, but the underlying principle is that a database's logical organisation does not have to expose all of its physical storage mechanisms.
Schema design is closely related to entities, attributes, relationships, keys and normalisation. An entity such as Customer can become a table, attributes such as name and email become columns, and relationships between entities become foreign-key associations or other relational structures. Primary keys provide entity identity, while constraints express integrity requirements. Normalisation can reduce unnecessary duplication and update anomalies by decomposing information into appropriately related tables. Denormalisation can deliberately introduce redundancy when justified by performance or reporting requirements. Schema design therefore involves balancing conceptual correctness, integrity, query patterns and operational performance.
A schema can evolve through database migrations. A migration is a controlled change to database structure, such as creating a table, adding a column, changing a constraint or creating an index. Application development commonly requires schema versions to evolve alongside application versions. Migration tools can record which structural changes have already been applied and execute new changes in a controlled order. Schema changes can be difficult for large production databases because adding or modifying structures may require significant time, locking, storage or data transformation. Backward-compatible migration strategies can allow old and new application versions to coexist during deployments.
Schema constraints are important because they enforce rules close to the data. Primary-key constraints prevent duplicate or null identities, foreign keys maintain valid relationships, unique constraints prevent duplicates where required, CHECK constraints enforce conditions and NOT NULL constraints require values. These rules reduce dependence on every application implementing identical validation correctly. However, not every business rule belongs naturally in a database constraint. Some rules require external systems, complex application logic or contextual information. A robust schema therefore establishes fundamental data-integrity guarantees while allowing application-level logic to handle rules that are inappropriate or impractical to enforce directly in the database.
Schema has a related but different meaning in some database products. In PostgreSQL, for example, a schema is a named namespace inside a database, and multiple schemas can exist within one database. In other systems, the terms database, schema and namespace may be used differently. In MySQL, schema is commonly used as a synonym for database, whereas in SQL Server a schema is a namespace associated with a database and is distinct from the database itself. Consequently, when technical documentation refers to "the schema", the intended meaning must be determined from the database system and context rather than assumed universally.
A database schema is therefore the organised definition that gives a database its structure, relationships, constraints and supported objects. It enables software to know what data structures exist and how they should be interpreted, while the database engine uses the definitions to enforce integrity and execute operations. A well-designed schema considers data semantics, keys, relationships, normalisation, query requirements, indexing, security and future evolution. The schema is not necessarily the same thing as the data itself: the data consists of stored values, while the schema defines the structures and rules under which those values are represented and managed.
