Single Database VS Multiple Databases for ERP Accounting Years Transactions Table?

Question

We have clients that every year make more than 2 million transactions in a double entry system (daily journal). Every year all transactions will be posted to the next year's opening balance as a single row for each account without posting any journal entries from the previous year. Every year starts with an empty transaction journal database.

There are two approaches:
1- multiple databases, one for every year's transactions
2- single database for all transactions by adding a period_year column

Problems in approach 1 (multiple databases):
1- many databases if a client has 10 years of accounting periods
2- if an old year's database is modified it must be posted to newer years' databases
3- if a client is hosted online, there's a restriction on the number of databases by the hosting provider
4- cross-database query may have problems, e.g. if a client needs a report from years 2016-2018

Problems in approach 2 (single database):
1- very large database size makes our technical support take time for backup & maintenance
2- if technical support updates a journal voucher without using target period_year it may destroy all accounting years
3- slow load in a GUI/UI layer
4- reporting is slow

The second approach may be good if it uses indexes, which are a good solution for:
-easy maintenance
-high performance
-database size
-cross-query
-GUI/UI responses
-flexible searching & CRUD SQL (update, insert, delete)
-reports & dashboards

Which is the best approach?

Answer 1

You question deals with what is known as partitioning and there are several approaches you could take which you will have to investigate the different strategies to fulfill your own specific needs. In my experience, dealing with a similar situation with a similar number of transactions in for some clients many more transactions, there was no need to implement partitioning as all solutions added unnecessary cost and complexity. Instead, all that was needed was to implement indexes and a maintenance plan.

As an alternative the the partitioning approach you describe you could partition transaction data and indexes of a single table by year using SQL Server but the solution could be expensive because it requires Enterprise SQL.

Taking two solutions your are considering, splitting your table into multiple tables isn't going to make the database size smaller. It will at least be the same size because if your single table contains 1 GB of data splitting into two will mean you ave two tables .5 GB each but your database will still be 1GB. In fact it will likely make your database larger because you will need to create additional indexes on the columns you will use to join the tables together. You will also need more complicated logic to query and update the data across multiple years.

The single table approach is most likely going to be the best way to go. It will be the easiest to maintain and develop against and have the lowest overall cost. High performance and GUI responsiveness can be achieved with the correct indexes and maintenance plans on the database. There is no need to cross-query tables.