3

I am designing a multi-client database (think SAS) in SQL server 2008 R2. During my research in this forum I found that segregating cient data using different database is preferred in the long run (when performance becomes an issue).

But I was wondering if for the short term (and for quick startup), is it a good idea to segregate client specific data by creating different schemas in same database ?

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
Supra
  • 1,612
  • 1
  • 18
  • 36

2 Answers2

4

The term for this kind of application is "multi-tenant" architecture. SO has a multi-tenant tag. Make it one of your favorites.

Multi-tenant architectures range from "shared nothing" to "shared everything". At the "shared nothing" end of the spectrum, you build one database per tenant. At the "shared everything" end, tenants share every table; each row has a tenant identifier that tells you who that row belongs to.

Between those two, you find one schema per tenant.

More details about the tradeoffs among cost, data isolation and protection, maintenance, and disaster recovery in this SO answer.

Community
  • 1
  • 1
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
2

My instict says NO. What do you gain in quick startup or in the short term with your proposed approach?.
Better to start from scratch with good habits: Keep an updated script with your schema and default values. You will be ready to go when another customer requires a fresh database. Not to mention that your client code need only to dinamically choose the right connection without any other concern

Steve
  • 213,761
  • 22
  • 232
  • 286
  • I have a spring/hibernate web application, so its easier for me to work with one connection string (which points to one database). And moreover, I can write (non-system) stored procedures in my database to create new schema on demand , along with creating basic tables etc when a new customer is created from the web app... – Supra Nov 09 '12 at 12:18
  • I'm not an expert on spring/hibernate, so I ask forgiveness for my ignorance, but how do you code your application? You need to pass always the schema to work on? For me this will be a nightmare scenario. Am I wrong? – Steve Nov 09 '12 at 12:24
  • Yes I do pass the schema info everytime. Not a nightmare (yet!) for me, as the application is relatively simple. And I will have 3-4 stored procs hooked up which will fetch me customer specific data based on the schema name I pass. – Supra Nov 09 '12 at 12:38