Modelling bank accounts in database diagrams

A common requirement for an application is that the bank accounts for customers or suppliers have to be stored. Before you begin modelling a schema, you should think about the following:

  1. Do you have to deal with non-IBAN banking accounts like e.g. legacy bank account numbers before IBAN?
  2. Does one customer or supplier only have one banking account?
  3. Is it relevant for your application to track from which bank account a payment had occurred?
  4. Is it important to store the bank accounts of the application owner?
  5. Should I use something like Stripe?

Storing the bank account information

Nowadays, https://en.wikipedia.org/wiki/International_Bank_Account_Number and BIC is used in Europe. But if you are dealing with countries like the United States, you have other bank account information, e.g. https://en.wikipedia.org/wiki/ABA_routing_transit_number.

For IBAN-only bank accounts, the following schema would be enough:

Table account_iban {
  id integer [not null, unique]
  iban char(34) [not null, unique]
  // if you want to also store the bank information
  // bank_id integer [not null, ref: > bank.id
}

Table bank {
  id integer [not null, unique]
  bic char(11) [not null, unique]
  name char(255) [not null]
}
  • an IBAN can have a maxium of 34 characters
  • an IBAN already contains the information to which bank it belongs to. You might want to add another table bank for caching reasons.

If you want to also use other bank account types like ABA, a more appropriate approach might be the following:

Table account_international {
  number char(255) [not null]
  banking char(255) [not null]
  type bank_account_type [not null]
  // apply a unique constraint of (number, banking, type)
}

enum bank_account_type {
	IBAN
	ABA
}
  • with help of the enum you can specify which type of banking information you are storing
  • a check for a valid IBAN could happen in your application's backend logic or as a database constraint

Multiplicity for banking accounts

A common approach is to add the banking account information directly into the customer table:

Table customer {
  id integer [not null, unique]
  name char(255) [not null]
  // ...
  iban char(34) [not null, unique]
  bic char(11) [not null, unique]
}

Even if it is totally fair, this modelling often requires a refactoring. Private citizens often only have one bank account, but businesses tend to have more than one. To model this fact, you need to have a 1:n relationship between the customer and his banking accounts:

Table customer {
  id integer [not null, unique]
  name char(255) [not null]
  // ...
}

Table account {
  id integer [not null, unique]
  iban char(34) [not null, unique]
  bic char(11) [not null, unique]
  customer_id integer [not null, ref: > customer.id]
}

I suggest you go with the 1:n solution right in the first place.

Storing historical banking account information

If it is important for your application to store from which bank account a transaction has happened, you are not allowed to delete any bank account information. Instead, you have to introduce a column like is_deleted and only mark the account as deleted. Your application backend logic and SQL queries have to deal with marked as deleted accounts.

Table customer {
  id integer [not null, unique]
  name char(255) [not null]
  // ...
}

Table account {
  id integer [not null, unique]
  iban char(34) [not null, unique]
  bic char(11) [not null, unique]
  is_deleted boolean [not null, default:`false`]
  customer_id integer [not null, ref: > customer.id]
}

Storing bank account information of the application owner

The owner of the application (e.g. your client, your boss or your employer) might also want to store its banking information. Depending upon the requirement from which accounts a banking transaction had been done, you might end up with the following blueprint:

Table customer {
  id integer [not null, unique]
  name char(255) [not null]
  // ...
}

Table account {
  id integer [not null, unique]
  iban char(34) [not null, unique]
  bic char(11) [not null, unique]
  // is_primary is used to mark this account as the "default" account for transactions or print-outs on PDFs
  is_primary boolean [not null, default:`true`]
  is_deleted boolean [not null, default:`false`]
  // customer can be null; if it's null then its the application owner's bank account
  customer_id integer [null, ref: > customer.id]
}
  • the foreign key customer_id can now be null. A customer_id with null would implicitly identify the bank account as an acocunt of the application owner. Your backend has to make sure that the key is set for non-owners.
  • the column is_primary is used to designate the given bank account as the default. This is used for PDF invoices and so on.

Externalize your bank account information

As you can see there are a some topics to think about. In the end you should ask yourself the honest question if you can move the dealing of bank accounts and payments in general to a payment provider like Stripe.