Single Database or Multiple Databases [x] Community Pick Community Pick is worth 500 points, assigned to Articles that earned 10 net helpful votes, and/or deemed helpful by a Page Editor.

One database for all clients or One Single database for each client

In today's IT Situation, many firms large or small have difficult in making decisions either to use single database for all the clients or one database for each client.  Since both have their own pros and cons, it is tricky when some factors like licensing, hardware, hosting costs involve.  The advantages and disadvantages of both options are listed below.

The word "Client" could easily be interchanged with Customer, though, it can also mean individual work stations in a large development environment such as a software house. Whilst some of the pros and cons equally apply to both situations, the intention here is in reference to individual Customers.

Advantages of Single Database for each client

• Performance is expected to be better with own databases. And it would be easier to move a customer that uses the system a lot to a separate set of servers. It is also fault tolerant, since only their own data exist in the database.

• If the client needs direct access to all their data;  in such an application it is obvious that each client needs to have their own database.

• For rapid development, using single database per customer is advisable. So it will be easy for the administrator to backup, restore, or delete a customer's data. So whenever the existing customer's subscription is expired, the database can be backed up and moved to the separate location.

• If the business plan is to have lots of small customers but with huge volumes of data, then single DB for each client is advisable.

• Clients typically feel safer with data isolation (at least they would probably not feel secure knowing their data is "side by side" with other companies' data )

• Single database can be easily Administered and Managed

• In terms of security, single database for each client is highly secured

• Single database can be Quickly implemented and Performance is rather good

• It is very easy to build new modules and add new features. No need to wonder, where to fetch data.

• Easy to operate - it's either working or not. It's actually Mark Twain's Dumbhead Walton principle: "Put all eggs in same basket and then be extremely careful".

• Single database have Higher resistance against data crashes

• In Single database each and every application remains unique or at least not dependent on others.

• If one application crashes, then database of other applications will not be impacted.

• Multiple databases are necessary if the site/application needs to be highly scalable (e.g. internet scale). For example, host each database on a different physical server

• If we think that an application might grow so much in little time. it is better to use different database for each client.

Disadvantages of Single database for each client

• Maintaining multiple databases is difficult. For example if we want to modify a table then we should do changes in all databases.

• Backup of data is not easy. We should do a separate backup for each database

• should pay for each database space for some providers

Advantages of Single Database for all clients

• Less Cost

• Grouping of data into multiple databases each with a significantly fewer number of tables.

• It is easier to maintain only one database instead of several. It is also a bit easier to collect statistics about the usage of the application if the database is shared. Administrator application is easier to develop as well

• In terms of flexibility, it's much simpler to use a single database with a single copy of the tables.

• It's easier to add new features

• It's easier to manage.

Disadvantages of Single Database for all clients

• If any Maintenance activity or Database is lost, then all applications will have difficulties to run

• A single database would most likely have a single set of servers, meaning one location. Connecting to those servers becomes slower based on numerous factors. The network needs to be robust and fast. The number of users could slow down a system that isn't scaled correctly.

• We can't take the database offline or down since all applications are running.

• If we want to do a backup and restore the database and give it to a set of clients running a single application, it will contain all client database information which is unnecessary

• Running into security threats and vulnerability is high

• Any query referencing this type of database will have performance impact

• More space is needed for overhead required to create a database and backing up a database

• With multiple customers in a single database, every SQL query is going to need to ensure that the data for the correct customer is chosen. That means that the SQL is going to be harder to write, and read, and the DBMS is going to have to work harder on processing the data, and indexes will be bigger

• Running accounting systems for different companies will not be acceptable, since no client will entertain this

• Application development is Harder. We need to keep track of every customer records.

• Less fault tolerant.

• Harder to move a customer to a separate set of servers if one customer is using much of the server resources

• For better performance we should ensure proper indexes and keys are used.

• Its harder to remove non existing clients records

Conclusion There are situations where either model might be preferable to match your requirements. However, you do need to consider all the pros and cons, which may include some / all / more of the above list, and make an informed and calculated decision. I hope this helps you in making some of those decisions.