views:

82

answers:

1

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!

+3  A: 

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.

Brian Hooper
aka "Database Normalization". This will also simplify changes that may come later and will be easier to maintain.
Matthew
Are there any performance issues to consider putting the data in separate databases as apposed to many tables in the same database? Is there a penalty to opening a new database connection?
Icode4food
The separate logical schemas ('databases') all reside in the same MySQL physical database, so no. In fact, since you can specify the storage characteristics of each differently it provides a way of removing performance problems by shifting schemas between storage areas, disks, etc.
Brian Hooper