Star and snowflake schemas are the most popular database schema designs. Their layouts resemble their names — a star and a snowflake, respectively. The star schema is straightforward, featuring a central fact table linked to one or more dimension tables. A snowflake schema represents a more intricate variation of the star schema. It involves a central fact table linked to multiple tables, which may connect to additional dimension tables.
What are database schemas? 5 minute guide with examples
Nov 20, 2023 - 11 min read
Christina Kopecky
Any application will require sophisticated ways to store and retrieve data. Some apps are more data intensive than others, but it is smart to budget for databases that scale well. More important, however, is the technical construction and design of your database. The relationships your data forms will lead to the construction of your database schema.
A database schema is an abstract design that represents the storage of your data in a database. It describes both the organization of data and the relationships between tables in a given database. Developers plan a database schema in advance so they know what components are necessary and how they will connect to each other.
In this guide, we will learn what a database schema is and why they are used. We will go through a few common examples so you can learn how to configure a database schema on your own.
Today, we will go over:
- Definition of terms
- What are database schemas?
- Benefits of database schemas
- Database schema types
- NoSQL example
- SQL server example
- PostgreSQL example
- What to learn next
Get hands-on with databases today.
Database Design Fundamentals for Software Engineers
Databases are universal - they underlie the technology you use every day. They're a crucial component of everything from telecommunications systems, banking systems, and video games to just about any other software system that maintains some amount of persistent data. In this course, you'll learn about the fundamental concepts of databases, why and when they're used, what relational databases are, and entity-relationship diagrams. You will also be exposed to techniques like normalization that help to increase the efficiency of databases. You'll wrap up with a look at the basics of Structured Query Language (SQL). After completing this course, you will be able to move onto more advanced courses involving database systems and SQL.
Definition of terms
Before we get too into the weeds with database schemas, let’s go over some database basics like the difference between relational and non-relational databases.
-
Data type: An attribute of a datum that informs how it will be interpreted. The main data types are: integer, float, character, string, and boolean.
-
Relational databases: Relational databases are organized depending on the internal relationships between data. The primary language used to create and manipulate relational databases is SQL
-
Database object: Data structures in a relational database that are used for the storage or reference of data. The most common is a table, but others include indexes, sequences, stored procedures, etc.
-
Tables: Tables are database objects that contain all the data in relational databases. They are formatted in a row-and-column layout similar to a spreadsheet.
-
Non-relational databases: A non-relational database is a database that is non-tabular. Data can be structured in many different ways. Some of the most common types of non-relational databases are: column-oriented, graph-based, key-value store, and document-oriented.
-
Key-value pair: A key-value pair is a tuple composed of one two linked data items. The key is a constant that can be called to retrieve the value that it is assigned to.
-
Entity: An entity in a database management context is a real-world item that is being stored.
-
Entity type: An entity type classifies entities by storing information that relates to entities.
What are database schemas?
When it comes to choosing your database, one of the things you have to think about is the shape of your data, what model it will follow, and how the relationships formed will help us as we develop a schema.
A database schema is a blueprint or architecture of how our data will look. It doesn’t hold data itself, but instead describes the shape of the data and how it might relate to other tables or models. A sample of data at any given point in time represents a database instance. It will contain all of the properties described in the schema.
Think of a database schema as a type of data structure. It represents the framework and arrangement of the contents of an organization’s data.
A database schema will include:
- Consistent formatting for all data entries
- Unique keys for all entries and database objects
- Each column in a table has a name and data type
The size and complexity of your database schema depends on the size of your project. The visual representation of a database schema allows programmers to structure the database and its relationships properly before jumping into the code. The process of planning a database design is called data modeling.
Schemas are important for designing database management systems (DBMS) or relational database management systems (RDBMS). A DBMS is a software that stores and retrieves user data in a secure way that follows the ACID concept.
In many companies, database design and DBMS responsibilities usually fall to the role of the Database Administrator (DBA). DBAs are responsible for all practices relating to maintaining and organizing data. These tasks range from managing data to designing and developing databases. They work alongside management teams to plan and securely manage an organization’s database.
Note: Some popular DBMS systems are MySQL, Oracle, PostgreSQL, SQL server, MariaDB, and dBASE, amongst others.
Benefits of database schemas
Database schemas are integral to the structure of modern databases. They inform how information is stored and retrieved. Understanding database schemas is crucial in understanding how the backend of a system will interact with the database. Understanding the finer points of database design will help you make educated decisions about system design architecture both in your career and in a system design interview.
Database design and development are more important than just study material though. We’ll cover some of the key features that database schemas of any type can bring to a storage system.
Accessibility and security
Information stored in schemas is more easily accessible as a result of separation. Information can be broken into multiple entities. This allows certain information to be readily accessible while maintaining levels of security for more sensitive data.
Administrator control
Since database schemas provide a clear logical outline for how data is stored, administrators can validate data in a secure and organized manner. As a result, administrators are able to ensure the database is functional and performant. Additionally, this level of manual control streamlines database management and compliance with design constraints and ACID properties.
ACID (atomicity, consistency, isolation, durability) is an acronym of the four main necessary properties that guarantee the consistency of data in the event of unexpected errors.
Documentation
Clear database schemas allow administrators, engineers, and other internal stakeholders to clearly communicate and understand design constraints and methods of aggregation. This level of clarity facilitates robust documentation that contributes to the usability of a database. Any good technology must be supplemented with effective and clear documentation to maximize its efficacy and reach.
Optimization
When it comes to making a database query more performant, often one of the first things that can impact speed is the database schema. The method for optimization depends on the type of database that you are working with. This short guide covers specifically how to design an optimized SQL schema.
Database schema types
There are two main database schema types that define different parts of the schema: logical and physical.
Logical
A logical database schema represents how the data is organized in terms of tables. It also explains how attributes from tables are linked together. Different schemas use a different syntax to define the logical architecture and constraints.
Note: Integrity constraints are a set of rules for a DBMS that maintain quality for data insertion and updates.
To create a logical database schema, we use tools to illustrate relationships between components of your data. This is called entity-relationship modeling (ER Modeling). It specifies what the relationships between entity types are.
The schema diagram below is a very simple ER Model that shows the logical flow in a basic commerce application. This ER model represents the informational relationship between a product and a purchasing customer.
The IDs in each of the upper three circles indicate the object’s primary key. This is the id that uniquely identifies the entry in a document or table. The FK in the diagram is the foreign key. This is what links the relationship from one table to the next.
- Primary key: identify a record in the table
- Foreign key: primary key for another table
Entity-relationship models can be created all sorts of ways, and online tools exist to assist in building charts, tables, and even the SQL to create your database from your existing ER Model. This will help to build the physical representation of your database schema.
Physical
The physical database schema represents how data is represented and stored on disk storage. To create a physical scheme, in a relational database we use SQL commands. For instance, in MySQL, you will use SQL to construct a database with tables.
Compared to the logical schema, the physical schema includes everything needed to create a relationship between tables such as indexes, linking tables, partitioned tables, etc.
Now that we are familiar with the basics of database schema, let’s look at a few examples. We will go over the most common examples you can expect to encounter.
NoSQL example
NoSQL databases are primarily referred to as Non-relational or Distributed Databases. Designing a schema for NoSQL is a topic of some debate since they have a dynamic schema. Some argue that the appeal of NoSQL is that you don’t need to create a schema, but others say that design is very important for this type of database since it doesn’t provide one solution.
NoSQL does not have the typical schema described above for the relational databases. However their underlying structure and design can be categorized as a schema.
This snippet is an example of what a physical database schema will look like when using Mongoose (MongoDB) to create a database that represents the entity-relationship diagram above. Click through the code tabs to see the different parts.
const mongoose = require('mongoose');const Customer = new mongoose.Schema({name: {type: String,required: true},zipcode: {type: Number,}})module.exports = mongoose.model("Customer", Customer);
The important thing to remember here is that in NoSQL databases like MongoDB, there are no foreign keys. In other words, there are no relations between the schemas. The ObjectId just represents an _id (the id that Mongo automatically assigns when created) of a document in another collection. It doesn’t actually create a join.
Get hands-on with databases today.
Database Design Fundamentals for Software Engineers
Databases are universal - they underlie the technology you use every day. They're a crucial component of everything from telecommunications systems, banking systems, and video games to just about any other software system that maintains some amount of persistent data. In this course, you'll learn about the fundamental concepts of databases, why and when they're used, what relational databases are, and entity-relationship diagrams. You will also be exposed to techniques like normalization that help to increase the efficiency of databases. You'll wrap up with a look at the basics of Structured Query Language (SQL). After completing this course, you will be able to move onto more advanced courses involving database systems and SQL.
SQL server example
SQL server is a relational database management system (RDMS) developed by Microsoft. It provides a software interface to the applications for storing, retrieving, and manipulating data, whereas the underlying database may be located on remote machine(s) accessible via the Internet. Microsoft provides several different editions of SQL server, including enterprise, standard, web, workgroup, etc. The SQL server 2022 version is Azure-enabled thus enabling database management from the cloud.
The schema of SQL server is similar to the traditional schema given that SQL server is a RDMS.
CREATE DATABASE example;USE example;DROP TABLE IF EXISTS customer;CREATE TABLE customer (id INT AUTO_INCREMENT PRIMARY KEY,postalCode VARCHAR(15) default NULL,)DROP TABLE IF EXISTS product;CREATE TABLE product (id INT AUTO_INCREMENT PRIMARY KEY,product_name VARCHAR(50) NOT NULL,price VARCHAR(7) NOT NULL,qty VARCHAR(4) NOT NULL)DROP TABLE IF EXISTS transactions;CREATE TABLE transactions (id INT AUTO_INCREMENT PRIMARY KEY,cust_id INT,timedate TIMESTAMP,FOREIGN KEY(cust_id)REFERENCES customer(id),)CREATE TABLE product_transaction (prod_id INT,trans_id INT,PRIMARY KEY(prod_id, trans_id),FOREIGN KEY(prod_id)REFERENCES product(id),FOREIGN KEY(trans_id)REFERENCES transactions(id)
PostgreSQL example
PostgreSQL is a free, open-source relational database management system that is highly extensibility and follows SQL compliance. In PostgreSQL, a database schema is a namespace with named database objects.
This includes tables, views, indexes, data types, functions, and operators. In this system, schemas are synonymous with directories but they cannot be nested in a hierarchy.
Note: In programming, a namespace is a set of signs (called names) that we use to identify objects. A namespace ensures that all objects are given unique names so that they’re easy to identify.
So, while a Postgres database can contain multiple schemas, there will only be one level. Let’s look at a visual representation:
In PostgreSQL, a database cluster contains one or more databases. Users are shared across the cluster, but data is not shared. You can use the same object name across multiple schemas.
We use the statement CREATE SCHEMA to get started. Note that PostgreSQL will automatically create a public schema. Every new object will be placed here.
CREATE SCHEMA name;
To create objects in a database schema, we write a qualified name that includes the name of the schema and the name of the table:
schema.table
The following example from the Postgres documentation CREATE SCHEMA to initiate a new schema called scm, a table called deliveries, and a view called delivery_due_list.
CREATE SCHEMA scmCREATE TABLE deliveries(id SERIAL NOT NULL,customer_id INT NOT NULL,ship_date DATE NOT NULL)CREATE VIEW delivery_due_list ASSELECT ID, ship_dateFROM deliveriesWHERE ship_date <= CURRENT_DATE;
What to learn next
Congrats! You now know the basics of database schemas and are ready to take your database design skills to the next level. Database schemas are vital to the creation of databases. Whether you use a NoSQL or SQL-based database, database schemas form the basis of your applications.
To continue your learning, the next topics to cover are:
- Three-schema architecture
- Entity-relationship models
- Relational model concepts
- Functional dependencies
- Normalization
To get started with these concepts, check out Educative’s one-stop-shop for database design: Database Design Fundamentals for Software Engineers. This course cover the fundamental concepts of databases. You will uncover techniques like normalization that help to increase the efficiency of databases. After completing this course, you will be able to move onto more advanced concepts like involving database systems!
Happy learning!
Continue reading about databases
Frequently asked questions
Haven’t found what you were looking for? Contact Us
What is the most common database schema?
WRITTEN BYChristina Kopecky
TRENDING TOPICS
