CODEWITHSUNDEEP
sqlsql-server
NotADog
NotADog

Reputation: 159

One Database or Many for multiple clients?

I'm creating a Microsoft SQL server that initially only served one client but am now looking to have many (Up to several thousand if things go well). The entire structure will be the same for each client with only the data within each table being client specific.

I am thinking of adding ClientID to almost all tables and referencing this in all functions (basically a where ClientID = @ClientID on every statement). Along with a Clients table that gains a new entry for every new client

The alternative being a create database [Client_Name] script that is fired whenever a new client joins the server to create another client specific database and all its associated structure and procedures.

Is there any advantage performance wise to either option?

Upvotes: 1

Views: 3875

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

The decision on how to structure such a database should not be made only on performance issues. In fact, that is probably the least of the issues. Some things to consider:

  • How will you manage updates to your application? Multiple databases can make this easier or harder.
  • Will individual clients have customizations? This favors multiple databases.
  • What are the security requirements for the data? This can go either way.
  • What are the replication and recovery requirements for the data? This would tend to be easier with one database, but not in all scenarios.
  • Will concurrent usage by different clients interfere with each other?
  • Will clients be responsible for managing their own data or is this part of your offering?
  • Is any data shared among clients? How will you maintain common reference tables?

In general, performance is going to be better with a single database (think half-filled data pages occupying memory). Maintenance and development will be easier with a single database (managing multiple client databases is cumbersome). But actual requirements on the application should be driving such a decision.

Upvotes: 4

Related Questions