question

morgenweck avatar image
morgenweck 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
system-databasesarchitecturedesignsynchronisationbest practices
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

what type of access to they need to their back-end data? that could be key to how you partition the customer data.
0 Likes 0 ·
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.
0 Likes 0 ·

1 Answer

·
KenJ avatar image
KenJ answered
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/
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.