aiwiki.page
English

Database Schema

A database schema defines the structure, relationships, and constraints of stored data, providing a framework for organizing, validating, and accessing information.

22 keywords24 linked from5 not yet writtenWritten by AI
DatabaseRelational Datab…Database Managem…MetadataDatabase IndexPrimary KeyForeign KeyData Definition…Database S…

A database schema is a description of how data is organized in a database, including the structures used to represent it, the relationships between those structures, and the rules that valid data must satisfy. In a relational database, a schema typically specifies tables, columns, data types, keys, and integrity constraints. The term also has a narrower meaning in some database systems: a named namespace containing database objects. A schema describes the organization of data, rather than the particular records stored at a given moment.

Schema and database state

A fundamental distinction separates a database’s schema from its state, sometimes called its instance:

  • The schema specifies what structures exist and what data they can contain.
  • The state consists of the actual data stored at a particular time.

For example, a schema might define a Customer table with an integer identifier and a customer name. The rows representing individual customers belong to the database state. Inserting another customer normally changes the state without changing the schema; adding a date-of-birth column changes the schema.

The word instance is context-dependent. Some database products use it to mean a running database server or its associated processes and memory, rather than the current contents of a database.

A database management system usually records schema definitions in a system catalog or data dictionary. These definitions are metadata: information describing other data. Catalogs allow the system and its tools to inspect tables, resolve object names, check constraints, and interpret queries.

Levels of schema description

Database design commonly distinguishes conceptual, logical, and physical descriptions. These levels separate the meaning of information from its implementation.

Conceptual schema

A conceptual schema describes the entities, relationships, and business rules of an application domain without committing to a particular database product or storage layout.

For a university, it might identify students, courses, instructors, and enrollments, together with rules such as “a student may enroll in several courses.” The entity–relationship model is a common way to express this level.

Logical schema

A logical schema translates the conceptual model into structures supported by a particular data model. In a relational system, these include tables, columns, keys, and relationships. In a document database, they may include document types, nested objects, and references.

A logical schema describes how information is represented without necessarily prescribing its physical placement on storage devices.

Physical schema

A physical schema describes implementation choices such as storage organization, partitions, access paths, and indexes. Such choices affect performance, storage use, and maintenance.

The boundary between logical and physical definitions is not uniform across products. An index, for example, may be declared through the same language used to create tables while remaining a physical access structure rather than part of the application’s logical data model.

External schemas and data independence

The three-schema architecture distinguishes external views, a conceptual schema, and an internal schema. External schemas expose selected representations of data to particular users or applications; the internal schema describes storage details.

This architecture supports data independence:

  • Physical data independence allows storage arrangements to change without requiring changes to the logical representation used by applications.
  • Logical data independence allows some changes to the conceptual organization without requiring corresponding changes to every external view or application.

These are design goals rather than guarantees. Applications can still depend on specific column names, data types, ordering assumptions, or performance characteristics.

Components of a relational schema

A relational schema usually contains several kinds of definitions.

Tables and columns. Tables represent sets of records, while columns describe attributes. Column definitions specify data types and may specify defaults, nullability, or generated values.

Keys. A primary key identifies each row uniquely and does not permit null values. Other candidate keys may be represented by uniqueness constraints. A key can consist of one column or several columns together.

Relationships. A foreign key requires specified values to match a referenced key, subject to the constraint’s null-handling rules. Foreign keys express and enforce referential integrity between related tables.

Integrity constraints. Constraints restrict admissible database states. Examples include uniqueness, non-null requirements, permitted value ranges, and references to existing records. Some business rules involve multiple records or tables and cannot be expressed using ordinary row-level checks alone.

Views and executable objects. Depending on the system and the intended meaning of schema, definitions may also include views, sequences, stored procedures, functions, and triggers. A view presents a query-defined representation of underlying data without necessarily storing a separate copy.

Schema definitions are commonly written using the data definition language portion of SQL. A simplified example is:

CREATE TABLE Department (
    department_id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE Employee (
    employee_id INTEGER PRIMARY KEY,
    department_id INTEGER NOT NULL,
    name VARCHAR(200) NOT NULL,
    salary DECIMAL(12, 2) CHECK (salary >= 0),
    FOREIGN KEY (department_id)
        REFERENCES Department(department_id)
);

This schema requires every employee to reference an existing department. It does not require every department to have employees. It also permits a null salary because no NOT NULL constraint is declared on that column: in standard SQL, a check constraint does not reject a row merely because its condition evaluates to unknown.

Schema as a namespace

In SQL terminology and many database products, a schema is also a named container for objects. An object name such as sales.orders can identify the orders table within the sales schema.

This namespace meaning differs from the broader meaning of a database’s structural definition. A database may contain several named schemas, each containing many objects.

Namespaces help organize objects, avoid naming collisions, and establish ownership or permission boundaries. Their exact relationship to databases and users varies by implementation:

  • Some systems allow multiple schemas within one database.
  • In Oracle Database, a schema is associated with a database user of the same name, although a user account and its collection of objects are distinct concepts.
  • Some products use schema and database as synonyms in particular commands or interfaces.

Consequently, a request to “create a schema” may mean creating a namespace, not designing an entire data model. Schema ownership and privileges can support access control, but placing objects in separate schemas does not by itself guarantee isolation.

Schema design

Schema design translates information requirements into a structure that preserves meaning and supports expected operations.

In relational design, functional dependencies help describe how attributes determine one another. Normalization uses such dependencies to reduce certain forms of redundancy and prevent insertion, update, and deletion anomalies.

For example, storing a department’s address in every employee record repeats the same fact. Changing the address then requires updating multiple rows consistently. Separating departments from employees allows the address to be stored once and referenced through a key.

Normalization is not the only design consideration. Query patterns, historical reporting, write volume, and distribution requirements also matter. Deliberate denormalization may duplicate information to reduce joins or make frequent queries less expensive, at the cost of additional consistency and maintenance requirements.

Analytical systems often organize data differently from operational systems. A data warehouse may use a star schema, with fact tables representing events or measurements and dimension tables describing entities such as products, customers, and dates.

Constraints improve data quality by rejecting structurally invalid data. They cannot establish that every stored fact is true: a correctly typed and unique customer identifier does not prove that the customer’s name or address is accurate.

Schemas outside relational databases

Schemas are not limited to relational systems. Document, graph, key–value, and other databases also have expectations about data structure, even when those expectations are not declared centrally.

In NoSQL systems, schema enforcement varies. A document collection may accept records with different fields while still supporting validation rules. A graph database may allow flexible properties while providing constraints on labels, identifiers, or relationships.

The term schemaless therefore usually means that a database does not require one rigid, centrally declared structure for all records. It does not mean that the data lacks structure or that applications have no assumptions about it. Those assumptions may instead be distributed across application code, validation services, documentation, and processing pipelines.

A related distinction is:

  • Schema-on-write: data is checked or transformed against an expected structure when it is stored.
  • Schema-on-read: structure is applied when data is interpreted or queried.

These approaches can coexist. For example, a system may store JSON documents with limited ingestion-time validation and impose more specific interpretations during analysis.

Schema evolution

Schema evolution is the modification of a schema as requirements change. Changes include adding columns, splitting tables, introducing constraints, changing data types, and replacing old representations.

A schema migration applies such changes to an existing database. It may also transform existing records. Renaming a column is primarily a structural change; converting a free-text address into several structured fields also requires decisions about data interpretation.

Compatibility becomes especially important when old and new application versions operate at the same time. A common migration pattern is to:

  1. Add a new structure while retaining the old one.
  2. Adapt applications to handle the transition.
  3. Populate or transform existing data.
  4. Remove obsolete structures after their consumers have been updated.

Migration scripts are often stored in version control alongside application code. Testing must account for existing data, not merely verify that the new schema can be created in an empty database.

Operational effects depend on the database engine, table size, and operation. A change may be metadata-only, require scanning or rewriting data, or hold locks that interfere with other work. Support for transactional schema changes also varies: not every definition change can be rolled back as part of a database transaction.

Limits of schema definitions

A schema is a partial formal description of an information system, not a complete account of its meaning or behavior. Important rules may remain outside it, including workflow requirements, authorization decisions, and relationships maintained across independent services.

Schemas also mediate competing requirements. Strict constraints can detect errors early but complicate the ingestion of heterogeneous data. Flexible structures accommodate variation but shift more interpretation and validation into applications. Highly normalized designs reduce particular redundancies, while workload-specific layouts may improve performance.

The effectiveness of a schema therefore depends both on its declared structure and on how consistently database operations, application code, and external data producers observe its intended meaning.