We are building a financial accounting application for users to manage single and multiple companies under them. The user can be an accountant with n number of companies under it or a single company itself. We are trying to understand how the database for such an application needs to be designed.
The ability of an accountant to see all open invoices across all the companies he is handling.
The ability to archive datasets of companies when they leave us.
The ability to fetch data from multiple companies under one accountant to generate reports.
There are three possible database structures but we need to know which one best suits us:
Have a parent database that holds all accounts and company information. Every company getis its own database to handle and store all transactions.
have a single DB to hold all users and company profile data and every individual company gets its own set of tables to store transactions.
Have a single DB that holds all the transaction data of all companies in a single table called transactions.
We are trying to understand which DB architecture suits us the best. I have MySQL/MariaDB in mind(solely because data is all relational) but if you think other databases would be better, i would definitely like to know more about it.