I am developing a textile shop application in .NET Core using the Entity framework. So I am developing it in Azure and using the same application for different shops using Shop ID as the primary key.
In theory that's all working fine for different kinds of shops especially since the concept is the same among any size of shops.
But the problem is, in a huge shop the transactions will be very high (Traffic). So they cant accept a delay in service provisioning. So when multiple huge shops using the same DB, that could end up drastic delay in delivery for smaller shops as well.
As a solution, I thought about using an On-Premise DB for big shops and sync it with the Azure SQL. But a question arises, how fast it should sync. Because there are public users are using an app for these shops to buy online. So if a delay in sync could end up in wrong stock info for online customers.
So which is the best and effective way to handle the scenario. I am happy without an OnPrem solution, but a great configuration in Azure DB itself.
Please suggest! Thanks in advance.
Best Answer
One option could be to have a bunch of databases in an azure sql elastic pool. It depend on the number of shops but ideally you cold have a db per shop.
If the trafic is high, try to reduce the in/out data amount between the database and the applications; options are caching or query optimization and good indexes strategy.