Database design - Multiple or single?

Databases
1

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?

Thanks for your help

2

What particular database system are you proposing to use - for many of them 100000 users whould be a medium sized system.

3

MySQL

I’m concerned that having many corporations sensitive data in a single DB isn’t a good idea…

4

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.

Would it be better to do this:

DB: MY_APP_DB

- TABLE: COMPANIES
---company

- TABLE: USERS
---user
---associated_company

- TABLE: FRUIT
---fruitaction
---associated_company
---associated_user

Or would it be better to do this:

DB: [B]APPLES_DB[/B]

- TABLE: USERS
---users

- TABLE: FRUIT
---fruitaction
---associated_user

DB: [B]ORANGES_DB[/B]

- TABLE: USERS
---users

- TABLE: FRUIT
---fruitaction
---associated_user

DB: [B]GRAPES_DB[/B]

- TABLE: USERS
---users

- TABLE: FRUIT
---fruitaction
---associated_user
5

If none of the data is shared between coproprations then with mySQL the best option would be to set up a separate database for each.

6

Ok, thanks!

7

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

8

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?

9

yes, it would

so would recording all the data on little slips of paper, sorting them manually, and adding up the totals on an abacus

i’m just sayin :wink:

10

Maybe look at the security from a different perspective.

If you make sure:

  1. the database and site are located in our web-hosts secure and PCI compliant facility.
  2. All data is stored in an encrypted database.
  3. All database data is backed-up;
  4. The servers are UNIX based, so they are far less vulnerable to to virus, malware or other hacking vectors/attacks than Microsoft based environments.
  5. It is run on dedicated not shared servers, which limits attacks that can come in shared hosting environments.
  6. The firewall infront of the database has highly aware intrusion detection enabled.
  7. The server is protected against ‘Brute Force’ attacks to prevent password cracking and unwanted server access.
  8. The server that holds the database and the database software are updated whenever security/functionality patches are published.
  9. 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.
  10. The servers are locked and bolted within their security and fire-protected chamber or server pod.
  11. 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.
  12. Make sure your web application :
    [LIST]
  13. Filter Input and Escapes Output using filtering and validation (on the server side)
  14. protects against cross-site scripting and cross-site request forgery
  15. does not allow session fixation
  16. limits the ability to hijack session
  17. protects against SQL injection
  18. properly hashing passwords and other sensitive data
  19. protects against brute force attacks
    [/LIST]

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.

Regards,
Steve

11

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.