asked
Setting up a multi institution SAAS system
I'm in the process of setting up a SAAS type application. My intent it to have one database that is accessed by various institutions (approximately 15 different institutions) that are set up with different DBid's. Is there a best practices for this type of set up? I can write all of the procedures based upon the the ID of the person pulling the data but I also need to set up total exports or access based upon the ID's. The various institution need to have access to their back-end data. Is it better to set up a system with a separate schema for each institution? or create views with the table names? or set up separate databases altogether? The data will be needed in real time. Would synchronization with filters work? Thanks
The database will contain data like Names, date of hire, publications written, department, grants they have received etc. Each institution would want to be able to attach business analytic tools to the data to create business matrix's. If it was a single institution I would give read only access to the single institution but with multiple institutions it becomes complicated for me.
it does get complicated once customers are allowed to hook up tools to it. If it's a fairly straightforward (small) schema, you might be able to grant them access via views that filter the data appropriately. Something to this effect could work: create view source_table_view as select src.* from source_table as src inner join security_table as sec on src.company_id = sec.company_id where sec.user_id = suser_sname() then hook up the analytic tools to the views. On the more complex side, I've seen one database per customer to ensure appropriate segregation of financial data. With this model, customers can hook up analytic tools to their database without concern that they might see another customer's data. The best practices tend to revolve around isolation of customer data, regulatory compliance and which approach works best with the way you architect and deliver your SaaS solution. Like with most best practices, I don't believe there is a blanket set of practices that can be described as 'best' across the board. With that being said, Microsoft, who have pretty deep experience with the SaaS model, have published *Design patterns for multitenant SaaS applications and Azure SQL Database*, which will probably have some great information to guide your own design decisions - [
https://azure.microsoft.com/en-us/documentation/articles/sql-database-design-patterns-multi-tenancy-saas-applications/][1] Despite its name, much of the pattern information in the article is platform agnostic, they just tie it in with Azure specific offerings. [1]: it+does+get+complicated+once+customers+are+allowed+to+hook+up+tools+to+it.++If+it's+a+fairly+straightforward+(small)+schema,+you+might+be+able+to+grant+them+access+via+views+that+filter+the+data+appropriately.++Something+to+this+effect+could+work:+++++create+view+source_table_view+++++as+++++++++select+src.*++++++++++from+source_table+as+src++++++++++inner+join+security_table+as+sec++++++++++++++on+src.company_id+=+sec.company_id++++++++++where+sec.user_id+=+suser_sname()++On+the+more+complex+side,+I've+seen+one+database+per+customer+to+ensure+appropriate+segregation+of+financial+data.++With+this+model,+customers+can+hook+up+analytic+tools+to+their+database+without+concern+that+they+might+see+another+customer's+data.++The+best+practices+tend+to+revolve+around+regulatory+compliance+and+which+approach+works+best+with+the+way+you+architect+and+deliver+your+SaaS+solution.++Like+with+most+best+practices,+I+don't+believe+there+is+a+blanket+set+of+practices+that+can+be+described+as+'best'+across+the+board.++With+that+being+said,+Microsoft,+who+have+pretty+deep+experience+with+the+SaaS+model,+have+published+Design+patterns+for+multitenant+SaaS+applications+and+Azure+SQL+Database,+which+will+probably+have+some+great+information+to+guide+your+own+design+decisions+-+
https://azure.microsoft.com/en-us/documentation/articles/sql-database-design-patterns-multi-tenancy-saas-applications/
Thanks- I think I may go with one web UI that changes connection strings based up login and then use different databases. It maybe a little more work but I think it will give the best security. Thanks again
17 People are following this question.
