We help IT Professionals succeed at work.

Single Large vs. Multiple Small SQL Databases

We are developing an on-line database application for tracking Athletic Data.I estimate the average customer will have 500 Athletes, 2,000 Injuries and 10,000 Treatments.

Customer
   +---- > Athletes
       +---- > Injuries
           +------ > Treatments

The advantage to a single SQL datababse would be:

1. One schema to maintain.
2. Ability for customers to compute collaborative statistics.

The downside would be:

1. Individual customer Queries are against the complete database.
2. Multiple customers accessing the same database concurrently.

The total number of records, even over 500 customers is small compared to SQL's capabilities, so I would expect a single database to be best. What are the considerations for creating a single large vs. multlple seperate (one per customer) databases of the same schema? My primary concern is querry speed.
Comment
Watch Question

Distinguished Expert 2019

Commented:
The complexity of separating the same type of data into many databases, would mean that your queries have to take into account the customer.  THe other issue is that it will limit your ability to analyze patterns. i.e. many runners during early may have injuries. etc.
The other complexity is the backup/restore of database data.

I do not believe the separation/setup of one database per customer, is worth the complexity nor will it make the data more secure.

In a multi setup, you are also running into the slowdown because the query is being made on a databases that is not currently in memory.  This would mean that memory may need to be freed up to load/access the individual db.

A good design and could speed up the query by use of index, etc.

Author

Commented:
I agree. Seems on large database would cache the same indexes (albiet larger) for all customers so on balance it's a win. Can't imagine Fidelity Investments or American Airlines have multiple databases. If you get a chance could you look at a question I posted on Custered Indexes. ID: 26256031.
Thanks much...
One database is the best approach.

Supposing that you'll have 100 customers, that means 100 database. In case of a schema change it will be a pretty big job to bring all databases up to date.

If you'll have performance issues (but I doubt it)  then table partitioning could be taken into account.
How about using partitioning as a solution ?

It basically offers same table access using SQL and normal operations of DB, however the table is physically split into multiple files, making the data retrieval  faster.

http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx
Commented:
Keep one database from manageability perspective and for the query performance build good indexes and choose to partition the database/tables according to your need.

For example:

Keep everything in DB1:

1. Build Indexes appropriately;
2. Store the data in files (as would have been the case with multiple database) by partitioning the data into different file groups as you would have done with different databases.
If query speed is primary then you could also:
-Ensure that ldf and mdf files are on different drives.
-Make secondary filegroup and move indexex to that partition
-Use covering index

and several others known database ways of optimalization.

I do not know much about your hardware, but this is not large database. (I have milions of record in one table).