I work for a franchise and am beginning the complete redesign of the application that each franchise uses to manage its daily operations. It functions as a point of sale and scheduling application. Currently, each franchise has its own local SQL Express database. The desktop application runs in their facility and reads from and writes to this database. On the corporate end, we need to get at aggregate information about all of the franchises so each time the application saves a local record, it messages a copy to the corporate server which inserts the data from all franchises into a shared database with an identical schema. Corporate can then run reports on the shared database containing records from all franchises. Why is it designed like this? At the time of its design (over a decade ago), stakeholders felt it was important that franchises be able to access their data without an internet connection.
Well times have changed and all involved are finally willing to move to a single centralized database with a web app front end to support all franchises instead of a desktop app. So in technical terms, we are designing a multi-tenant app with a shared database. Like I said, we actually already have the database on the corporate end, but it's not supporting the moment-to-moment in-franchise reads and writes.
To give you an idea of the amount of data we're dealing with, there are currently a few hundred franchises, 3 million customer accounts of which a much smaller portion is actually active customers, and 8 million purchases. One of the largest tables contains weekly customer calendar entries and has over 91 million rows. Again, each of these records is specific to a single franchise but they will be stored together. E.G. tbl_Customers has 3 million customers and each has an fk_FranchiseID pointing back to tbl_Franchises.
The database will need to support the daily franchise operations (web app most likely using an Entity Framework-based data layer), reporting for both individual franchises and aggregate corporate reports, and some customer-facing website functions such as displaying schedules and customer account information. I think the aggregate reports, expense, and ease of adding new franchises are the factors driving the idea of a shared database. Additionally I think the plan is currently to have a load balancer with two web and two database servers.
With the caveat that there is a lot I still don't understand and am still learning about SQL Server, my biggest concerns are:
- Partitioning franchise data. The franchise web app will need to retrieve records only for its current user's franchise. In the current database, some tables are 5-10 joins away from tbl_franchise. Having to do all of these joins just to filter on the franchise ID when otherwise no joins might have been required seems like it could really harm performance. Queries need to run at least as fast as they do today against their local private databases so the filtering by franchise needs to be negligible. As wrong as it seems, would it be better to sacrifice normalization and include the franchise ID directly in some or all of these child tables? Or are the joins negligible if the keys are indexed and the query is not using any other columns from the joined tables?
- Privacy. We need to make it impossible for a user or a developer to intentionally or accidentally pull up data that belongs to the wrong franchise. SQL Server 2016 row level security seems to be an option for this. This, again, leads me to point 1, though. A whole lot of joins will be required to associate the rows with their franchise record in the predicate function.
- Lock contention. If we can use indexes to safely put sifting through other franchise's records aside, for the most part, the franchise management app should be doing short read and write operations. But still suddenly having all users potentially accessing these tables at the same time has alarm bells sounding in my mind. Additionally, we need to continue offering reporting to the franchises for their individual data, and to corporate for the aggregate data. A few of these reports are pretty computationally heavy and cannot necessarily accept dirty reads so I'm worried if a report is running, we might have a few hundred franchises unable to use the system.
So the overall question is: what strategies and techniques can be used to efficiently segregate tenant data in a shared database such as this?