Lisandro is a Database Architect from Rosario, Argentina. With almost 20 years of experience in Oracle and SQL Server, among other database engines, he currently works as Sr. Data Engineer on OZ Digital Consulting. He is also a member of both the Oracle ACE program and the Argentina Oracle User Group. In his free time, he enjoys watching his soccer team (Rosario Central) with his two sons.
This article will guide you through the necessary steps to create a database model for a banking system. We’ll use the Vertabelo online data modeler to define all the required entities and the relationships between them.
Bank legislation varies from country to country, and not all banks offer the same products and services. In this article, we’re going to create a generic ER diagram for a banking system. This database design may require adjustments or modifications based on your own requirements and your local legal and industry regulations.
Vertabelo is a powerful web-based tool that allows us to create logical and physical data models. We can also use it to automatically generate SQL scripts so we can deploy the model tables in a physical database. If you want to learn more about the differences between logical and physical models, you can read What Are Conceptual, Logical, and Physical Data Models?.
Let’s begin by creating a logical data model that can be later used to create both a physical model and the SQL scripts to implement it. (We won’t go into the process of generating the physical model from the logical model and then generating code from the physical model to implement the database, but we’ll include links to other articles on those topics.)
The first step to create a model in Vertabelo is to click the Create New Document button on the menu bar:
Then, select the document type (here, “Logical Data Model”) and press Next:
The last step before we start modeling is to provide a name for the data model:
Now we have an empty model. We’re ready to start designing our entities. If you’re new to database design, you may want to read Data Modeling Basics in 10 Minutes to familiarize yourself with the basic concepts.
Once we have a model, we need to start adding the entities that will be part of the system we are designing. We can do this by selecting the Add New Entity button on the diagram taskbar, as shown below:
After that, we can click on any empty space of the diagram and the new entity will be created.
Let’s review the different entities and learn how to create them in our model.
This entity will keep information about each person that interacts with the bank, either as a customer, an employee, or any other role.
Note: A Person can be an individual or an organization.
The first thing we need to do is provide the desired entity name and a note about this entity. We can do this using the first section of the Property Panel, which is situated to the right of the diagram:
We are going to define the following attributes for the Person entity:
We can define all the attributes for this entity the Entity Properties panel:
Note #1: the “M” toggle box indicates if the attribute is mandatory. The “PI” toggle box indicates if this attribute is part of the primary identifier (primary key) of the entity.
Note #2: Clicking on the gearbox symbol next to each attribute opens a wizard that simplifies picking the correct data type.
Note #3: All attributes mentioned in this article will be mandatory unless explicitly identified as optional or nullable.
Although we have already selected the PersonID attribute as the PI in the Attributes section, we can define a name for the primary identifier. We can also modify the attributes that are part of it in the following section of the Entity Properties panel:
The primary identifier will be converted in the physical model into a primary key (PK). The article What Is a Primary Key? will help you understand the importance of keys in a database model.
Besides the surrogate primary key that we have defined for the entity, we can also create additional identifiers (natural keys) that help uniquely identify each row. In this example, we are going to use TaxIdentifier as an additional identifier in the next section of the Entity Properties panel:
Now the first of our entities is complete and we know how to create entities and their attributes in the logical model. Let’s review the other entities we are going to include in our system.
This entity will keep basic information about the different branches or offices of the bank. It will have the following attributes:
This entity will store information about the persons that are also bank employees. Besides having all attributes of a Person , they will have these additional attributes:
Note #1: Each employee is related to a Person and to a Branch . We will see how to create those relationships later in this article.
Note #2: Positions could (and should) be normalized in a separate entity. To keep the model simple, we are using a denormalized version.
This entity will store information about the persons that are also bank customers. Besides having all attributes of a Person , they will have these additional attributes:
Note #1: Each employee is related to a Person . We will see how to create those relationships later in this article.
Note #2: Customer type could (and should) be normalized in a separate entity. To keep the model simple, we are using a denormalized version.
This entity keeps information about the different accounts each customer or group of customers can have in the bank. It has the following attributes:
Note #1: Each account is related to one or more Customer and to a Branch . We will see how to create those relationships later in this article.
Note #2: Both account type and status could (and should) be normalized in separate entities. To keep the model simple, we are using a denormalized version.
This entity keeps information about the different loans that the bank grants to customers. We are going to create it with the following attributes:
Note #1: Each loan is related to one Customer . We will see how to create those relationships later in this article.
Note #2: Both loan type and status could (and should) be normalized in separate entities. To keep the model simple, we are using a denormalized version.
Loans usually have a scheduled number of payments that include both principal and interest. The LoanPayment entity in our model represents these scheduled payments. It has the following attributes:
Note #1: Each loan payment is related to one and only one Loan and can be paid in one or multiple Transaction s. We will see how to create those relationships later.
Each operation performed in a bank is usually represented by one transaction (e.g. deposit, withdrawal) or multiple transactions (e.g. account transfers). The Transaction entity in our model will handle those operations. It has the following attributes:
Note #1: Each transaction is related to one Account and can be related to another Transaction (for example, when a transfer between two accounts is performed). It can also be related to an Employee if it was performed manually in an office/branch. And as we mentioned before, it can also be related to a LoanPayment . We will see how to create those relationships later in this article.
Note #2: Transaction types could (and should) be normalized in a separate entity. To keep the model simple, we are using a denormalized version.
After creating all the entities, we will have a data model with no relationships established:
Once we’ve defined all of our entities, we need to establish the relationships between them. Depending on the type of relationship (one to many or many to many) we will use one of the following buttons on the diagram taskbar:
Let’s analyze the relationships in our model.
Each Customer in our model is a single Person , but not all persons are customers. We need to establish a 1:N (one-to-many) relationship between the two tables, N being either 0 or 1.
We need to select the Add 1:N Relationship button, click on the Person entity, and – keeping the mouse button pressed – move the mouse over the Customer entity and release the button. We should see that a line between the two entities has been included in the diagram:
Now we need to define some additional features of the relationship. We’ll edit the properties using the Relationship Properties panel on the right side of the diagram; click on the relationship line to access it.
In this case, we have changed the relationship type to “One to One” and indicated that the Person is mandatory in the relationship, meaning that each Customer must be related to a Person.
Each Employee in our model is a single Person, but not all persons are employees. Once again, we need to establish a 1:N relationship between the two tables, N being either 0 or 1.
All employees work at a branch of the bank, and every branch can have many employees, so we need to create a 1:N relationship between the Branch and Employee entities.
A customer can usually have multiple accounts; depending on legislation and regulations, most systems allow shared accounts between multiple individual customers (family members, etc.). To represent this in our system, we are going to create a many-to-one (M:N) relationship between Customer and Account .
After creating the relationship, we need to verify that the relationship is mandatory on the customer side. There can be customers without any account, but each account must be related to at least one customer.
This is a 1:N relationship, since a single branch can have multiple accounts and each account must belong to one and only one branch.
This is usually a 1:N relationship, since loans are granted to a single customer but a customer can have multiple loans.
This is also a 1:N relationship. We need to ensure that both sides are mandatory, since each payment must belong to a loan and each loan must have at least one scheduled payment.
A loan payment is usually paid in a single operation, but most banking systems will allow for partial payments. This means that we need to create a 1:N relationship between the Payment and Transaction entities but with both sides being non-mandatory.
This is also a 1:N relationship, since transactions involve a single account and accounts can have multiple transactions.
Most transactions are performed directly by users via banking apps, online banking, or ATMs. However, there are still some operations that can be made at a bank office and involve an employee. To support this, we need to establish a 1:N relationship between the Employee and Transaction entities and ensure that none of the sides are mandatory.
There are several scenarios where a transaction needs to be related to another transaction in the system. Some of them are:
In this case, we need to create a 1:N relationship to the same Transaction entity. We achieve this by selecting the 1:N button and just clicking on the desired entity. We also need to ensure that both sides of the relationship are not mandatory.
Let’s look at our complete logical diagram of a generic banking system:
The next step – after checking and validating the banking database model you currently have – is to transform it to a physical model, which includes database-specific information. After that, you can use Vertabelo to automatically generate the DDL script to build the database. As promised, here are some resources to walk you through these processes:
And if you are curious about other business-specific data models, check out the other example ER diagram articles in this blog!
Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts. Subscribe
