Schema is a database term that defines the structure of data and how it’s organized. It describes the relationship between tables and their columns, as well as the rules for entering data.
For example, if you have a table with customer information and another table that has product information, you can create a schema to indicate which customers bought which products.
Schema is also used to define relationships between data in a database. For example, one table might contain sales data for a specific product line over time; another table might contain sales data for all product lines. If there’s no connection between the two tables (that is, if they don’t share any columns), then they won’t be able to share any data because they don’t share any columns!
What Is Schema In Database With Example
When constructing the backend of an application, you need to take into account how the frontend will talk to the backend. More important, however, is the 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.
Get hands-on with databases today.
Try one of our 300+ courses and learning paths: Database Design Fundamentals for Software Engineers.
Start learning
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. An entry in our database will be an instance of the database schema. 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:
- All important or relevant data
- 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 style 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 ensuring that data analysts and database users can easily access information. They work alongside management teams to plan and securely manage an organization’s database.
Note: Some popular DBMS systems are MySQL, Oracle, PostgreSQL, Microsoft Access, MariaBB, and dBASE, amongst others.
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. It explains a product to a customer who buys a product.
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 stored on disk storage. In other words, it is the actual code that will be used to create the structure of your database. In MongoDB with mongoose, for instance, this will take the form of a mongoose model. In MySQL, you will use SQL to construct a database with tables.
Compared to the logical schema, it includes the database table names, column names, and data types.
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 Database. 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.
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.
CustomerSchema.jsProductSchema.jsTransactionSchema.js
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.
Try one of our 300+ courses and learning paths: Database Design Fundamentals for Software Engineers.
Start learning
SQL server example
An SQL database contains objects such as views, tables, functions, indexes, and views. There are no restrictions on the number of objects we can use. SQL schemas are defined at the logical level, and a user that owns that schema is called the schema owner.
SQL is used for accessing, updating, and manipulating data. MySQL is an RDBMS for storing and organization.
We can use the SQL Server CREATE SCHEMA to make a new schema in a database. In MySQL, schema is synonymous with database. You can substitute the keyword SCHEMA for DATABASE in MySQL SQL syntax.
Some other database products draw a distinction. For example, in the Oracle Database product, a schema represents only a part of a database: the tables and other objects are owned by a single user.
Note: In SQL, a view is a virtual table that is based on the result-set of a statement. A view contains both rows and columns.
Primary keys and foreign keys prove useful here as they represent the relationship from one table to the next.
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 scm
CREATE TABLE deliveries(
id SERIAL NOT NULL,
customer_id INT NOT NULL,
ship_date DATE NOT NULL
)
CREATE VIEW delivery_due_list AS
SELECT ID, ship_date
FROM deliveries
WHERE 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!
database table schema example
Here are the 5 key Database Design along with the Schema Example:
- Schema Example: E-Commerce Transaction
- Schema Example: Online Banking
- Schema Example: Hotel Reservation
- Schema Example: Restaurant Booking
- Schema Example: Financial Transaction
Schema Example: E-Commerce Transaction
Take the example of a customer on an e-commerce website. Two important components in a schema are the primary key and the foreign key. When generating an ER (entity-relationship) diagram, like the one shown above, the object’s primary key can be the IDs, which uniquely identifies the entry in a table. The foreign key, which is the primary key for another table, links the relationship from one table to the next.
SQL schemas are defined at the logical level, which is typically used for accessing and manipulating data in the tables. SQL servers have a CREATE command to create a new schema in the database.
The following creates a schema for customers, quantities, and price of transactions:
CREATE TABLE customer ( id INT AUTO_INCREMENT PRIMARY KEY, postalCode VARCHAR() default NULL, ) CREATE TABLE product ( id INT AUTO_INCREMENT PRIMARY KEY, product_name VARCHAR() NOT NULL, price VARCHAR() NOT NULL, )
Schema Example: Online Banking
The following is a sample code of creating schemas like above with regards to online banking:
CREATE DATABASE -- Table structure for table `account_customers` DROP TABLE IF EXISTS `account_customers`; CREATE TABLE `account_customers` ( `Account_id` int(10) unsigned NOT NULL, `Customer_id` int(10) unsigned NOT NULL, PRIMARY KEY (`Customer_id`,`Account_id`), KEY `fk_Accounts (`Customer_id`), KEY `fk_Accounts1_idx` (`Account_id`),
Schema Example: Hotel Reservation
The above schema can be modified based on business rules such as number of requests per customer, number of assignments by admin, multiple rooms on the same booking date, payment types, etc.
Here is a sample code of creating the schema:
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 ) …. ….
Schema Example: Restaurant Booking
In this schema, a unique id can be given to a customer. It can be read as ID or customer_id. Similarly, the user table, ingredient, and menu will be incorporated with business rules. A sample code to generate schemas like the one shown above:
CREATE TABLE `restaurant`.`user` ( `id` BIGINT NOT NULL AUTO_INCREMENT, `fName` VARCHAR(50) NULL DEFAULT NULL, `mobile` VARCHAR(15) NULL, `email` VARCHAR(50) NULL, ….. …. PRIMARY KEY (`id`),
Schema Example: Financial Transaction
The above Schema Example represents a star-type schema for a typical financial transaction. As discussed in a star schema, you can see that this design looks clean and easy to interpret for future collaborations across teams. The transaction table is connected to the table of account holders as well as the banking staff who are at the helm of the transaction.
CREATE DATABASE example; USE example; DROP TABLE IF EXISTS customer; CREATE TABLE customer ( id INT AUTO_INCREMENT PRIMARY KEY, CurrencyCode VARCHAR) default NULL, ) DROP TABLE IF EXISTS product; CREATE TABLE product ( id INT AUTO_INCREMENT PRIMARY KEY, GENERAL_LEDGER_ CODE VARCHAR(50) NOT NULL, price VARCHAR(7) NOT NULL, qty VARCHAR(4) NOT NULL ) …… ……