Single Database Vs Multiple Database

Question

Hi Everyone,

we have web application which stores information in backend SQL 2014. 
currently the way is set up that central database stores all key information for the customers and then we have seperate database for each customers. 
Now tables structure for each customer's database is same for particular product. 
e.g. if one customer1 DB has tables almonds, oranges and Customer2 DB has tables lemon, citrus the structure is same just table is different depends on what kind of famrs they have. This tables belongs to Product 1 they purchased. This tables doenst have any reference to key since it will have redundant data. 

In future if they will buy second product, Table structure for the product will be different then product1 but it will be same along each customers database.

Currently we are small in size but expecting to grow very very big with all kind of spatial data and Raw data with images , GIS data etc..
Also with Different prodcuts offering like product1, product2, product3 etc..

question is ..
Is this good database design for future reference to hand heavy load? 

Should move forward to have one big database contains all information?

Also keeping central Database on SQL and each customer DB keep on 'Not Only SQL' platfrom ( MongoDB, Cassendra) will be good approach?

Sorry If I missed any information. feel free to ask me and would be happy to provide. 
Thanks

---

Thank you very much for your time and effort to answer this post. Please Mark As Answer if it is helpful. \\Aim To Inspire Rather to Teach Best -Ankit

Answer 1

>Should move forward to have one big database contains all information?

That is preferable for two reasons:

1. Generally maintenance is simpler

2. DB design can avoid redundant data and cross database referencing

---

---

Kalman Toth Database & OLAP/AI Architect SQL Server 2016 Database Design
New Book : Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

Answer 2

Thanks Kalman,

 we have  redundant data  for each customers individual database ( not in central DB) and think it will be huge  ahead of time.

---

Thank you very much for your time and effort to answer this post. Please Mark As Answer if it is helpful. \\Aim To Inspire Rather to Teach Best -Ankit

Answer 3

Practice has shifted on this question over the last few years from preferring a multi-tenant database, to preferring single-tenant databases.  If you have a database-per-tenant you have to automate all your deployment and maintenance, but you get:

1) Robust separation of tenant data

2) Per-Tenant Query Plans instead of one-size-fits-all query plans

3) Per-Tenant code deployment and patching

4) The ability to scale out your tenants across servers

5) Per-Tenant ad-hoc reporting

6) Per-Tenant Backup and Restore

If you have thousands of small tenants then still a multi-tenant database is the way to go, but if you have 10s to 100s of tenants, or 1000s of tenants but they are large, then the scales tip to database-per-tenant.

In both SQL Server and Azure SQL Database you provision resources in a pool and then assign databases to the pool of resources.  In SQL Server the "pool" is an Instance, and in Azure SQL Database it's an Elastic Database Pool, but the idea is the same. 

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 4

Hi,

if you have or expecting more number of customers, then go for expanding the database servers horizontally. Have a template database and then make new databases from it as you customer base grows. This way you can limit some databases exclusively for some customers (single-tenant) or also have multiple customers DB (Multi-tenant). You can also do maintenance individually. This will work if you maintain the same structure or schema across databases.

Answer 5

Thank you David and Vinay for writing on this. 

Currently we are with few customers but we going big with lot customers and massive amount of data going to be processed per customer . 

Also analytics team having problem to do analytical stuff since information is scattered within each database and central database. 

Any thought on this as well?

---

Thank you very much for your time and effort to answer this post. Please Mark As Answer if it is helpful. \\Aim To Inspire Rather to Teach Best -Ankit

Answer 6

Ideally you should have different database (OLAP) for analytics and have data copied to the DW database (BI Model) from your (OLTP) database(s). 

if you have one or many OLTP databases, the data should get summarized in to a Data warehouse database using methods like SSIS/Service broker..etc