Tracking Database for Photographers [Design/Setup Questions]
Posted: Thu May 23, 2013 12:07 pm
I have a lot of experience developing websites, but not much developing larger systems with many users. So I'd really like some advice in general database structure for a site consisting of many users and much more data (such as a forum). Currently I'm using MySQL via PHP's MySQLi extension.
The site I'm working on is a database for photographers/filmmakers to store/create information such as: projects, freelancers, equipment, invoices, expenses, etc. It's basically just a lot of tables with a lot of cross-referenced data. What makes it awesome is only that it very specifically relates to the needs of the user while remaining highly customizable.
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.
So, what I'm asking is this: With this amount of data involved, what are the most important things I need to know? I would assume MySQL could handle it, structured properly, but am I wrong? What kind of basic optimization/structure do I need to watch out for past auto_increment IDs and cross reference tables?
Also, if anyone has any advice past database structure, that'd be helpful too.
I'd really appreciate some direction in this. Thanks!!
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!!