From a security standpoint, I would have a seperate database for each company.
eslater:
I have an application written in CSLA that potentially thousands of companies could be using. I am using SQL Server and my question is am I better off creating a separate database for each company or storing all the data in one database?
I currently have 18 companies running off one database without any problem but want to make sure this is the way I should be going before I get too far into this.
Thanks for any comments.
Where is the CompanyID being set/stored? In the app? What happens if Company A guesses/discovers Company B's CompanyID?
I also don't like storing CompanyID with every record in every table, and also having to have it in every WHERE clause in every sproc or view. It seems like a lot of storage/work to try to keep info seperate that shouldn't be together in the first place.
Unless there is a compelling reason to have ALL the data available in one place or share a lot of data between companies. I would seperate them. It will also make migrating one or more companies to a different box a lot easier.
The counter-counterpoint is, do you want to make sure that ALL customers/clients are updated prior to upgrading the DB? Multiple DB's allow you to roll out upgrades one customer at a time. It also allows you to offer customer specific customization if necessary.
Hi
I am also interested in this. I am interested to hear from those who use the companyid approach as I have similiar scenario .... rather that companies I have a web app that covers different regions/offices and need some confidence to use the officeid (rather than separate databases for each office) approach.
Any contributions welcome
Griff
I'm the cheif architect for a company that offers SaaS.
Our hosted small company application is having a couple of thousand customers in the same database. No client will ever know it's id or being able to input it anywhere to trick the system to bring back data from some other client. If you are planning to grow big, it's best to have as few instances as possibly from a maintanence perspective. Database Indexes and webserver caching is probably the two toughest problems for us. Caching since no data is shared between clients, and idexes since we have to index to the mean, not how a specific clients data neads are. WE rarely have any performance problems though!
We also have single database for each hosted large client.
Large clients gets there own url and virtual directory, so its always easy to figure out what database they should connect to.
Sceptical
We are also developing a SaaS app and have similar issues to deal with. We went with an isolated archtiecture (single-db/single-tenant) because it is the easiest to implement during the development phase. However, I do have concerns about operational support and costs after implementation, which tends toward a shared model (single-instance/multi-tenant or multi-instance/multi-tenant for very large implementations).
The shared model would use horizontal partioning and a hybrid security model consisting of both impersonation and trusted subsystem. Key wrapping is also a consideration in order to simplify all of this.
Wondering if anyone here has had any experience with the latter?
It really depends on the application you're creating and the data you store. What would be the consequences be of a malicious user retrieving data for another company? At a guess you have a Company table, so what would it take for an amateur hacker to find a hole in your app and run a select on that table? How likely is that to happen? How many developers are working on the product and what's the likely hood they bypass the stored procedures and run a sql statement direct? How much data is being stored by each company? Is it vast amounts? Maybe you'll have issues when you have x amount of companies using it? What happens if you lose data? One unhappy client is better than all of them! What are the consequences of a server going down? Is the application exposed as an application or a website? etc.
You need to weigh all that stuff up against the overhead of having seperate databases/servers. Chances are it will be fine to have a single database, it just depends on what your priorities are I guess.
We are in the process of looking at possible ways of addressing this exact scenario. We provide a service to +- 50 companies all of which are hosted on the same database. From an operational/end user point of view this does seeem to be the best way as we have teams that could work on multiple companies at the same time, switching between companies within the same instance of the application provides a huge productivity gain for a user.
Copyright (c) Marimer LLC