My question is very similar to this question but a bit more specific.
My application has multiple companies and multiple users per company. It makes the most sense to me (at this point) for each company to have a "private" set of tables. This makes security extremely simple as I don't have to worry about JOIN-ing up my structure tree to be sure I only get data for the specific company. I can also extend the mysqli database extension and have it put a prefix on the table names in the query so that I never have to worry about security while writing my queries.
One other major advantage that I can see is that if one of the companies needs a customization, I can modify their specific tables and not have to take into account everyone else. The way that my app is designed it is extremely modular and implementing custom code is very simple.
There are some disadvantages that I can see but so far it seems that the above advantages would out-weigh them. The above proposed system does sort of grate on my (possibly) hyper-normalized database schema preferences up to this point. Another obvious disadvantage is implementing schema alterations but I can script them and be safe enough. One point that I'm not sure about is performance. If I have MySQL working with so many tables, will I make bottlenecks for myself?
I look forward to your thoughts!
