I would say that my tables and queries are both pretty well structured, but I don't know much about what that means for a large-user system. I know forums like phpbb and such use their own database class, but I'm not familiar how this might (or might not) improve speed. I also know that forums tend to slow down after a few hundred thousand entries. Which might be an issue here.
To give a small example of the current setup, the system is more-or-less divided very simply as so:
companies: main account
users: each company will probably have ~1-10 users
expense-types: ~2-10 custom expense types per company with additional data such as a custom icon, description, etc
expenses: ~1000-10000 (?) expenses per company
project-types: ~2-20 custom project types
projects: ~10-1000 projects per year
project-expenses: ~10-500 expenses per project
project-[many other tables]: ~10-500 additional entries per project
The gist of which is that there will be a huge amount of data. Right now I have a handful of accounts with a fraction of the above data and everything seems to work fine. But I'm worried about extending this to a dozen accounts and, later, up to a couple hundred or more.
Also, if anyone has any advice past database structure, that'd be helpful too.
I'd really appreciate some direction in this. Thanks!!