Main Problem :
We Have Clients That Maybe Every Year Make More Than 2 Milion Transactions of Double Entry System / AKA ( Daily Journal )
SO Every Year All Transactions Will Posted To Next Year As Opening Balance As Single Row For Each Account. Without Post Any Journal From Previous Year.
so what i mean every year start by clearly with empty Transaction Journal Database.
So That we need to know about Second Approach ( Single Database For All Years )
For Example Adding Column Called Period_Year
There`s Two Approaches Here :
1- Multiple Databases For Every Year Transactions
2- Single Database For All Transactions By Adding Period_Year Column
Problem I Focused In 1st Approach ( Multiple Database ) :
1- Many Databases If Client Has 10 Years Accounting Period
2- If Old Years Databases Modified It Must Posted To New Years Databases.
3- If Client Hosted Online, There`s Restrict In Number Of Databases By Hosting Provider
4- Cross-Query Maybe Will Be Problems, If Client Need Report From (2016 - 2018) Years.
Problem I Focused In 2nd Approach ( Single Database ) :
1- Very Very Large Database Size. Make Our Technical Support Taking Time For Backup & Maintenance
2- If Technical Support Update Journal Voucher Without Using Target Period_Year
It maybe destroy all accounting years !!
3- Slow Load In UI/GUI Layer.
4- Reporting Are Slowly Also.
I Know 2nd Approach Maybe Will be great if using Indexes >>..
*So please inform me about which are good solution for : *
-Easy Maintenance
-High Performance
-Database Size
-Cross-Query
-GUI/UI Responses
-Flexible Searching & CRUD SQL ( UPDATE, INSERT, DELETE )
-Reports & Dashboards
So Which one is good approach ? Single Or Multiple Database ?
Question is Fully Modified. Please anyone focus same problem or need to give me some suggestions / tips will be appreciated thanks.