ASP News->Querying a Multi-Tenant Data Architecture Options

[Vault9]

During my career as a consultant and web developer I've worked on a number of "software as a service" (SaaS) web applications. Such web applications are designed to be used
by multiple customers, where each customer is typically a company that has dozens or hundreds of users (its employees or customers) that access the system. One such SaaS-style
application that I've worked on for the past several years is a hosted application used by hospitals and clinics to manage patient information, doctor's appointments,
billing, prescriptions, and so forth.



When developing a SaaS-style application you need to decide how to store each customer's data. The article [url="http://msdn.microsoft.com/en-us/library/aa479086.aspx"]Multi-Tenant
Data Architecture[/url] examines different data architectures for SaaS applications. One option presented in the article is to store all customer data in a single database.
This approach requires adding a CustomerID column to all of your tables to determine which data belongs to what customer. Moreover, whenever you query the
database to display data in a web page you need to make sure to filter the data by the CustomerID of the currently logged on user. Another option is to use a
separate database for each customer. This approach lessens the risk of a user somehow seeing or modifying data for a different customer and this level of isolation may be
necessary for applications that store sensitive data, such as health care and financial applications. The hospital software SaaS application I work on uses such a separate
database data architecture.



One downside to using separate databases is that it is harder to run a query against all customer data. For example, if you find an anomaly in the data for one customer - say,
a patient record that has a street address specified but no city specified - it can be difficult to quickly determine whether the same anomaly exists in other databases. This
article discusses different ways to query multiple databases and shows a simple, web-based tool I've created and routinely use for those SaaS-style web applications I work
on that use separate databases to store different customer data. Read on to learn more!

Read More >



Source: http://www.4guysfromrolla.com/articles/090909-1.aspx

[Vault9]