How To Design An ER Diagram For An Invoice Management System Database Design Invoices And Payments

                       Download PDF

How to Design an ER Diagram for an Invoice Management System

Over the past 25 years, Jeffrey has worked in various roles in financial services software, with a focus on transaction banking and automation with artificial intelligence methods. He has extensive knowledge and expertise in financial payments, combating financial crime, and virtual account management. His experience spans various disciplines ranging from architecture and design to product management and sales.

Do you need to create an ER diagram for an invoice management system? Maybe you’re just curious about how the process would work. We are here to help.

So, let's get started on creating the model for your invoice management database. To begin, we will create an entity-relationship diagram (ER diagram or ERD), which is a model of the database that represents the information to be stored by the invoice management system.

When you create a database model, you need to start with planning how to create your model. Then you can move on to designing the database and generating the actual database. In this article, I will focus on the design step, but there are other steps involved in the process; for more details, check out A Detailed Guide to Database Schema Design.

As you are probably aware, there are different types of data models; we generally speak about conceptual, logical, and physical models. The conceptual model is a high-level, non-technical overview, while the logical model is more detailed and the physical model is created for a specific database management system.

What Is an Invoice Management System?

Invoices are a business’s lifeline. Inefficient invoice processing leads to poor cash flow management and possibly additional fees.

Invoice management is a business function that is responsible for managing invoices to customers and processing invoices received from suppliers. An efficient system can streamline processing, manage payments, and reduce loss from late payments, mistakes, and fraud. Modern systems automate and manage every part of invoicing and payment, i.e. creating and sending invoices, tracking payments, and generating reports.

Challenges Related to Invoice Management

Invoice management is a time-consuming and often manual task; much invoicing is still being done using paper-based invoices.

From these paper invoices, we need to input information into our invoice management system. This most often requires manual data entry which is error-prone and causes inaccurate or incomplete data. In addition, there is little standardization in the format and content of invoices, which produces additional inaccuracies. The result of these mistakes can be irregularities in payments. This can result in penalties and lost revenue (and make your company look bad).

Conditions Met by An Invoice Management System ERD

When we are creating a database for an invoice management system, the ER diagram will include all the information related to invoices. It also needs to show the limitations and restrictions that the data must meet.

Our invoice management system database model will be the logical and physical designs to store invoice information in the database. So, let's start with some information management.

Information Needed in an Invoice

As I am based in the EU, I will consider what information is legally required in Europe for an invoice. Under EU directive, a full invoice must contain:

• The date that the invoice was issued.
• A unique number that identifies the invoice; this can be a sequential number or some other identifier.
• The supplier’s VAT (Value-Added Tax) identification number.
• The customer’s VAT identification number.
• The full name and address of the supplier.
• The full name and address of the customer
• Either a description of the quantity and type of goods supplied or the type and extent of services provided.
• The unit price of goods or services, not including taxes, discounts, or rebates (unless these are part of the unit price).
• The dates of the transaction and payment (if these are different from the date that the invoice was issued).
• The VAT rate applied.
• The amount of VAT that is payable.
• A breakdown of the payable VAT amount by tax rate.
• The foreign currency rate (if the currency is different from the supplier’s domestic currency).

A Step-by-Step Guide to Creating an ER Diagram for Invoice Management

This step-by-step guide is just an overview, but there are several other guides on our website for beginners. We also offer database design walkthroughs for more advanced modelers. For this guide, I’ll use Vertabelo to create the entity-relationship diagram, but there are other online data modeling tools if you'd like to use another program.

Logical Model

Our ERD for an invoice management system needs to manage various types of information. The logical model shows the key entities:

• Invoice : Stores the data of each invoice. The data model shows the information related to an Invoice and is linked to an Order .
• Order : The customer's order, which includes various Product (s).
• Product : The products supplied by this company.
• Customer : Data for each customer, including their location.
• Address : The customer’s address. One customer may have multiple addresses, i.e. a billing address, shipping address, and postal address.

The goal is to manage information about invoices for customer orders and store this important data. Each table will need a unique or primary key; as a modeler, you will need to define what those keys are. The primary key must be chosen with care. I have used surrogate or artificial keys to uniquely reference each row. You can think of a surrogate key as a counter that gives a unique ID number to each row.

We also show the relationships between tables with foreign keys. A foreign key is a field within one table that is linked to another table‘s primary key; they are the "relationships" of our relational database model. For example, you would have a foreign key to link the Customer and Order tables, as orders are placed by customers.

One useful feature that the Vertabelo modeling tool offers at this stage is real-time validation of the model. You can create a model that is not valid, but only by ignoring the warnings from Vertabelo. For example, I have not defined the length of the attribute Type within the Address table; this has been marked with a warning flag by Vertabelo.

After you create this basic skeleton of the model, you can add the details of what information is stored in each table. You add attributes that describe each entity and the data types of the attribute, whether it is a string, number, date, etc.. For example, the attributes we use to describe each invoice include an invoice number, invoice date, the details of the supplier (such as name, address, and VAT number), and the details of the customer, as required by the EU directive for an invoice.

We also establish the relationships between entities based on primary and foreign keys. For example, the Invoice_InvoiceNumber field in the Invoice_Item table points to the InvoiceNumber field in the Invoice table. This allows us to link each item within an invoice to the invoice to which it belongs.

Vertabelo enables you to automatically create the physical model from your logical model by selecting what database management system you will be deploying your database on. Then Vertabelo assigns the DBMS-specific data types to each attribute of your logical model.

Physical Model

One important point to make is that the physical model is not completely normalized (more about that in a second). The invoice tables are denormalized so that we can keep the exact information of the invoice separate from the data that could change in the Customer - Address - Order - Product tables.

The reason that this is done is to keep an exact, “frozen”, official, legal snapshot of the information that was present in the invoice tables on invoice creation. Usually, we normalize database tables; normalization eliminates redundancies. However, this is an example where we denormalize to maintain data history for invoice management. A customer might change addresses and we might change the price of a product, but the information that is stored for an invoice must be exactly the information when that invoice was created.

Here’s what the physical data model would include for our invoice management system:

• Invoice : Stores a copy of Customer In an expanded model, it would also store supplier information.
• Invoice_Item : The individual items that are included on the invoice; these are often referred to as "lines" on an invoice. As each item on an invoice may have a different taxation rate, we must have the VATRate per item in the Invoice_Item .
• Customer_Order : Links the order to an individual Invoice – this is done once the customer's order is finalized and an invoice must be created.
• Customer : All information related to customers. Please note that the PrimaryPhone attribute of the Customer table is defined so that a phone number up to 15 digits and the “+” sign can be stored as the phone number. This is the phone number format specified by E.164. In addition, Customer table includes a Timezone attribute to store the international code for the customer’s time zone (rather than any national designation, like the Eastern, Central, Mountain, and Pacific time zones of the USA). You can find tips about ensuring that your data model is globalized in 7 Key Things to Remember About Data Model Globalization.
• Customer_Address : Allows multiple addresses to be linked to a customer. Each address can be of a different type through Address_Type .
• Address_Type : Stores information describing if an address is used for shipping, billing, postal, etc.
• Address : Stores address information, including street address, post code, etc. I frequently find data models are too Amero-centric. Thus, I have defined a flexible PostCode There is no constraint on the information that may be stored in PostCode, as some countries have alphanumeric postcodes. And rather than specifying that we will have a state (or province, or territory), I have used the more generic Region in the Address table. If you're curious about managing global addresses in a database, I would refer you to Beverly Hills 90210 and ZIP+4: Handling Addresses in Data Models or 7 Key Things to Remember About Data Model Globalization.

There are more detailed steps to optimize the physical model, such as creating indexes, creating views, and schema partitioning. We won’t go into them now, but you can look them up later.

From a Physical Model to the Actual Database

Now that we have fleshed out the physical model, we can create the actual database. To do that, we need to generate the Data Definition Language (DDL) scripts for this database. Vertabelo includes a feature to generate DDL/SQL scripts from the physical model. Below you can see an excerpt of the generated DDL for the invoice management database:

-- Created by Vertabelo (http://vertabelo.com) -- Last modification date: 2023-10-12 15:58:03.543 -- tables -- Table: Invoice CREATE TABLE Invoice ( InvoiceNumber uniqueidentifier NOT NULL, InvoiceDate date NOT NULL, TransactionDate date NULL, PaymentDate date NULL, Supplier_Tax varchar(14) NOT NULL, Supplier_Name varchar(100) NOT NULL, Supplier_Address_Line1 varchar(70) NOT NULL, Supplier_Address_Line2 varchar(70) NULL, Supplier_PostCode varchar(16) NOT NULL, Supplier_City varchar(35) NOT NULL, Supplier_Country varchar(2) NOT NULL, TaxAmountPayable money NOT NULL, ForeignCurrencyRate decimal(5,4) NOT NULL, Customer_Tax varchar(14) NOT NULL, Customer_Name varchar(100) NOT NULL, Customer_Address_Line1 varchar(70) NOT NULL, Customer_Address_Line2 varchar(70) NULL, Customer_PostCode varchar(16) NOT NULL, Customer_City varchar(35) NOT NULL, Customer_Country varchar(2) NOT NULL, Customer_Order_ID int NOT NULL, CONSTRAINT Full_Invoice_pk PRIMARY KEY (InvoiceNumber) ); -- Table: Invoice_Item CREATE TABLE Invoice_Item ( …

Electronic or Paper Invoices?

Electronic invoices are treated as equivalent to paper invoices under the EU directive mentioned previously. Any invoice must contain the elements mentioned above. The directive gives flexibility so that countries can impose the period of time for which invoices must be safe-stored. It also allows member states to decide if invoices that were sent on paper must be stored as paper and if e-invoices can remain in electronic form.

In some countries, it may be required to store an electronic invoice in an unalterable form. This means it cannot simply be rows in a database, as these could be altered. For example, you may be required to store an electronic PDF copy of the issued invoice within your database. There are two main approaches to storing files in a database:

1. Using a BLOB or CLOB (Binary or Character Large OBject) to store the file within a row in the database.
2. Storing the file on the file system and recording the metadata (file name, file system location, date, etc.) in the database.

Database storage is usually more expensive than file system storage. However when transactional integrity is important, then storing the file in the database is appropriate; it’s complex to manage integrity between the database and file system.

Document and Share Your Model

One recommendation that I would make is to ensure that the usage of each object in your model is clear. Wherever things aren't crystal clear, add notes to your model. Vertabelo also supports version control. This allows you to maintain different versions of your physical model and automatically generate the SQL required to migrate your database between different versions.

You should also think about the extensibility of your model. Obviously, we could add more information to this model, like suppliers’ deliveries and the status of an invoice and its payments. Making notes is a good way to enable changes to a model further down the line.

What if you’re working with a database but you don’t have access to its schema? In this case, you need Vertabelo’s reverse engineering feature. This lets you automatically build a data model from an existing database or DDL script. It takes the actual, current database definitions and reverse engineers the current database model. There’s more information on how to turn a data definition into a model here.

Ready to Design Your Own ER Diagram for an Inventory Management System?

Database design may seem overwhelming; in some cases, creating your model may be daunting. Other articles on our blog have numerous example ER Diagrams if you need some inspiration. You can also get advice on where to find database schema examples. Carefully review the ER diagrams you find and consider your particular requirements. Using the right tools and following database design best practices and tips will make designing your database easier.

Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts. Subscribe