October 18th, 2009, 07:17 PM
Single or Multi Database Format? Need Input!
I am developing a web application that will allow companies to register and use the software. My single most concern is the fact that if 1 database gets comprimised I do not want everything to fail. This is my first time developing a multi-company software package. Would creating a database for every company when they sign up be a reasonable design? It would be done on the fly via code. Or, should I stick to a single database with a table for company & ids?
October 18th, 2009, 08:10 PM
If you're going to use the same login in the backend to access the different databases, it is pretty much pointless to use different databases, since the attacker can compromise the other databases as well using an appropriate query.
Up the Irons
What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
"Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
Down with Sharon Osbourne
"I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo
October 18th, 2009, 11:05 PM
The advantage of different databases is reliability (one outage doesn't affect other companies), segmentation, and simplicity. This disadvantage is much greater cost (hardware, people) and reduced productivity (management of servers). Very quickly you'll find that the benefits are theoretical and the liabilities great.
Security is handled by layers to reduce when broken. The application should have security to detect invalid operations. The network should restrict which machines can connect to others, the database can restrict the queries, etc. If you design with security in mind, like you are trying to do, then you can easily put into place the appropriate restrictions to safe-guard the system.
Its far more likely that the multi-database model becomes less secure because it requires too much manual intervention, resulting in poorly chosen passwords and obvious security holes. Its time consuming and costly. Try to automate the on-boarding process as much as possible to remove the human equation and design security into the architecture.
I've seen small companies hit the scalability wall with the per-db model due to its cost. I've seen larger companies hit the scalability wall with a single-db due to performance, but they then move to a sharded model. I've never seen anyone correct coarse and move to a per-db model. The closest was to segment out a very large partner due to their special needs.
October 19th, 2009, 12:43 PM
I second most of the previous statements and would add that you should establish and test a backup procedure. Also, if robustness/security is critical, do not put the db on the same machine as the web site (or even at the same location) and deploy some intrusion detection software. You could also resort to clustering.
I no longer wish to be associated with this site.
October 19th, 2009, 06:22 PM
There are other considerations. If you're planning to host your website, some hosts will charge you per additional database. If you have shared data you'll want to incorporate a single shared database for data that's common for all companies (maybe). You lose a lot of ability to get consolidated statistics and usage data when each customer is in different db's.
Think it out and use a design that best fits your needs. There is no 'one size fits all' solution.
Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.