Ok, I’m designing a web app that will be used by corporations. Each corporate account will have X number of users under that account. My 2 main concerns are scalability and data protection (data loss, corruption etc.). The users would be entering information that would be very important to the company. I want the DB design to be scalable to handle up to 100000 users with alot of data.
So i’m wondering would it make sense to create a new database for each corporate account to address these concerns? or should i stick with a single database?
Here’s a better explanation (sorry for the corny example):
Say I have 3 fruit warehouse companies:1 apple company, 1 orange company, and 1 grape company. My app would track incoming and outgoing pieces of fruit for the company.
it’s not just whether there is any data shared, you also have to be sure that there are no queries which are expected to produce reports across corporations, e.g. how many truckloads of fruit did all of your fruit warehouse companies ship last year
counting and other queries across databases are difficult
I’m thinking that I would have one DB with corporation info and user info (all users and all companies would be in this DB). Then each corporation’s private DB would contain all of there data.
I think (I haven’t tried it yet) that I could store the DB name in the corporate/user info DB and loop through and determine how many private DBs I have and what they are named. From that I could loop through and query each individual DB for whatever info I want and add them together to get any info I want across all corporations. Wouldn’t that work?
Maybe look at the security from a different perspective.
If you make sure:
the database and site are located in our web-hosts secure and PCI compliant facility.
All data is stored in an encrypted database.
All database data is backed-up;
The servers are UNIX based, so they are far less vulnerable to to virus, malware or other hacking vectors/attacks than Microsoft based environments.
It is run on dedicated not shared servers, which limits attacks that can come in shared hosting environments.
The firewall infront of the database has highly aware intrusion detection enabled.
The server is protected against ‘Brute Force’ attacks to prevent password cracking and unwanted server access.
The server that holds the database and the database software are updated whenever security/functionality patches are published.
There is logged secure access to the room where the database server is stored to avoid theft or damage to the server hardware that hosts the database.
The servers are locked and bolted within their security and fire-protected chamber or server pod.
The whole site that connects to the database is SSL secured so personal information, phone numbers and email addresses, financial information, and username and passwords can not be eavesdropped upon and stolen when being submitted to the web site.
Make sure your web application :
Filter Input and Escapes Output using filtering and validation (on the server side)
protects against cross-site scripting and cross-site request forgery
does not allow session fixation
limits the ability to hijack session
protects against SQL injection
properly hashing passwords and other sensitive data
protects against brute force attacks
You do these things that really should be part of the design of an application that has sensitive data, then it is reasonible to store all the data in one database and make the database management, design and scripting easier and more maintainable as the sytem grows.
If there are reports that are expected to produce reports across corporations then the data that those reports reference is shared as it isn’t only referenced from within the one corporation but is shared with the reports that run across multiple corporations.