CODEWITHSUNDEEP
sql-serversql-server-2008database-designmulti-tenant
Supra
Supra

Reputation: 1652

multi client database design : is separation by schema a good design?

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 ?

Upvotes: 3

Views: 2671

Answers (2)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95761

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.

Upvotes: 4

Steve
Steve

Reputation: 216353

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

Upvotes: 2

Related Questions