#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2006
    Posts
    13
    Rep Power
    0

    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?

    Thoughts?
  2. #2
  3. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,607
    Rep Power
    4247
    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
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jul 2005
    Location
    Bay Area, California
    Posts
    841
    Rep Power
    1682
    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.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    May 2004
    Posts
    3,417
    Rep Power
    887
    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.
  8. #5
  9. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,424
    Rep Power
    4539
    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.
    ======
    Doug G
    ======
    The man who doesn't read good books has no advantage over the man who can't read them.
    --Mark Twain

IMN logo majestic logo threadwatch logo seochat tools logo