Database Schemas
What Is a Database Schema?
Simply put, a database schema is a formal description of the structure or organization of a particular database (DB). The term database schema is most commonly used for relational databases, which organize information in tables and use the SQL query language. Non-relational (or “NoSQL”) databases come in several different formats and don't have a “schema” in the same way that relational databases do (although they do have an underlying structure).
Related Reading: SQL vs. NoSQL: 5 Critical Differences
There are two fundamental components of any database schema:
- Physical database schema: The physical database schema describes how you physically store data in a storage system and the form of storage used (files, key-value pairs, indices, etc.).
- Logical database schema: The logical database schema describes the logical constraints applied to data and defines fields, tables, relations, views, integrity constraints, etc. These requirements provide useful information for programmers to apply to the physical design of a database. The rules or constraints defined in this logical model determine how data in different tables relate to one another.
The definition of physical tables in the schema comes from the logical data model. Entities become tables, entity attributes become table fields, etc.
6 Types of Database Schemas
Learn more about the six most common database schema types below:
- Flat model: A flat model database schema organizes data in a single, two-dimensional display—think of a Microsoft Excel spreadsheet or a CSV file. This schema is best for simple tables and databases without complex relationships between different entities.
- Hierarchical model: Database schemas in a hierarchical model have a “tree-like” structure, with child nodes branching out from a root data node. This schema is ideal for storing nested data—for example, family trees or biological taxonomies.
- Network model: The network model, like the hierarchical model, treats data as nodes connected to one other; however, it allows for more complex connections, such as many-to-many relationships and cycles. This schema can model the movement of goods and materials between locations or the workflows required to accomplish a particular task.
- Relational model: As discussed above, this model organizes data in a series of tables, rows, and columns, creating relationships between different entities. The next section and the rest of this guide will focus on the relational model.
- Star schema: The star schema is an evolution of the relational model that organizes data into facts and dimensions. Fact data is numerical (for example, the number of sales of a product), while dimensional data is descriptive (for example, a product’s price, color, weight, etc.).
- Snowflake schema: The snowflake schema is a further abstraction on top of the star schema. It contains a fact table that connects to a dimensional table, expanding the descriptiveness possible within a database. As you might have guessed, the snowflake schema gets its name from the intricate patterns of a snowflake, where smaller structures radiate off of the central arms of the flake.