0

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?

Brian
  • 4,480
  • 1
  • 22
  • 37
SQLLogic
  • 1
  • 1
  • 1
    You don't have to make this decision until you actually need to, at which time it will be a good problem because you'll have the money to throw at it. Stick with a single database until your growth demands multiple database servers, geographic diversity, etc. – Robert Harvey Aug 10 '16 at 21:24
  • Database per customer, Schema per product – Matthew Whited Aug 10 '16 at 21:33
  • http://programmers.stackexchange.com/a/279100/15162 has a list of resources which may be helpful for your research. – Brian Aug 11 '16 at 15:44
  • The Discussion going on to change current infrastructure with projecting that going to have massive amount of processed GIS data with images ( which goes on SSD). Also analytical team having problem to do analytics works because of desperate db for each customer . But GIS team suggesting to have desperate DB as per customer. should go with SQL + NOSQL or SQL+DW+NOSQL? – SQLLogic Aug 11 '16 at 18:38

1 Answers1

2

How oranges are different from lemons? I mean, are they “structurally different” like a Product is structurally different from Person or from Comment, or they are just different forms of the same object, such as Smartphone and Tablet are simply different forms of a device?

In the first case, distinct tables make sense. In the second case, there would usually be one table ConsumerDevice which will store both forms of objects. Sometimes, the distinction wouldn't be clear, and the structure may change over time.

As for the one database per customer, I can see at least three reasons to use this approach:

  • If customer's data belongs to the customer, you may easily do a database dump, without having to carefully filter the data which belongs to other customers.

  • If you have a specific offer where a customer may host your service in-house, this is it: migrating the database from your servers to customers' becomes very straightforward, and doesn't affect in any way other customers.

  • You may upgrade the product selectively for some of your customers before pushing the new version to everyone.

However, if I were you, I would think twice before using this approach. Database maintenance may quickly become a nightmare. It could work if you have experienced DBAs who have already experienced the case where they had to maintain hundreds or thousands of similar databases. Otherwise, avoid this at all costs. A fully automated infrastructure is also a requirement here: “Remote Desktop/let's patch this quickly” approach is out of question here.

Arseni Mourzenko
  • 134,780
  • 31
  • 343
  • 513