I am implementing an accounting System in Codeigniter using MySQL Relational Database. I'm facing some issues. I am recording all transactions in a "Transaction Table" which can be seen as General Ledger in accounting terms. As there could be more than thousand users of the system and there could be billions of transactions in my "Transaction Table". For extracting accounting reports like Balance Sheet, I need to search all of the transaction of that specific User and then perform other operations to form an "Accounting Report"
Now Come to the issue, as transactions growth is way too high which would definitely slow down my system. How can I handle this issue?
I came to a solution which is "account closing", If I close accounts every month, restrict users to perform Create,Update and Delete functionality then I'll be able to use pre-calculated values of accounts for my accounting reports, but I can not restrict user to VIEW previous transactions, so in that case system will have to search out for his transactions from billions of transactions which is not solving my problem.
I'm thinking about transferring closed transactions to separate table of every user which can solve the problem, but my Database is normalized(3NF). Would it be a good idea to create separate tables for every user while creating that user's account and manage that newly created table's relation?
Your model may be wrong. You assume one credited and one debited account per transaction. Often there are 3 or more accounts taking part in transaction.
Regarding your scale question - don't worry about it now. Get your product to be successful. If you get close to bilion entries hire a consultant to help you out, there are many techniques he or she could use, sharding being one of the keywords.
If you don't assume being able to afford consultatnt with 1 bln entries for multi-user accounting system, your business model is flawed and you're getting yourself in big trouble down the road. If that's the case it's better to rethink it now.
By my estimate with your current structure the table would hold about 0.5 GB per bilion entries. MySQL should handle this with no issue, unless your server has space limitations.
