Multi-company web application database security

Question

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!

Answer 1

Your proposal sounds reasonable to me. I would suggest that instead of prefixing your tables with the company name, you store the tables for each company in a separate schema. That way you can have tables with the same name, reducing your problems in the code, and have each set of tables protected by a different username and password in a convenient manner. Backups and replication would then all be distinguishable at need.

Lookup tables could be stored in yet another schema to which all users have access.