May 17, 2011 at 6:42 pm
I'm setting up a SaaS product using DotNetNuke.
DNN has a database which contains users (using the Membership Provider) and information pertaining to the site/portal. Modules can also create tables/views/SPs/etc in the main database or use another one (where login info would have to be added to web.config).
I do have some database design experience, but this is proving to be a bit beyond me.
Since each company's data should not be visible by another, this brings up my question:
Which would be better, a separate database or everything in one single database, and why?
Also, consider that these will have to be updated about every month and as security patches come out.
I understand that having multiple databases would require me to have a database "key" or login to the other databases...recommendations on how to handle this...
Whereas if I were to have a single database, I would have to have a Company ID on every field, this would impact all of my queries because of the required where clause.
Any Suggestions?
May 17, 2011 at 6:55 pm
In the business world, I would first consult with qualified lawyers to determine if per chance with a single database, if confidential. or business confidential data was visible by one company from another companies entries. The potential price tag for that type of slip up, could be sufficient enough to decide what is the best approach. Since it is humans doing the data entry, assigning password, deciding who can see what, the probability of error could be significant.
May 18, 2011 at 12:57 am
Are you running this in a single portal or will each company be given a "site". Either way you are going to need to be sure to segregate the data for each company. Now that raises some questions about your target audience. Depending on the audience you may have things like HIPAA compliance to consider. And your process has to be well tested to make sure that you don't have "spillage" which in DoD terms is BAAAAD! Accidental disclosures between companies can open you to litigation. It also shows that you haven't taken due diligence/due care as serious as you should.
If you use seperate portals then portalid is a logic choice to seperate the data by.
Seperate database do provide a means to protect each companies data at the expense of managability.
Depending on your app functions and compliance related issues I don't think a solid recommendation can be made without additional data.
CEWII
May 18, 2011 at 1:21 am
Elliott,
Thanks for your reply.
The system is a church management system. This is the description from by business plan: The system is a web-based Software as a Service (SaaS) platform which assists churches with managing their contacts with their congregation and community. (Australian market only)
Basically, they can record contact details about their congregation and community members with whom they interact. Also, if there is some pastoral care (counselling/support) issues that needed to be recorded, they would be also. Other personal information such a pray requests and information on children and compliance with Australia's working with children checks would also be recorded.
My research doesn't show any particular requirements, which is one reason Google hasn't released Google Health for Australia yet. My lawyer said to just make sure that I have a policy in place on how data is handled. I was considering looking at using the HIPPA regulations, just to have a benchmark to aim for. (I'm actually a US citizen living abroad.)
As far as access separation, I was considering two options:
1.) Separate Portals
2.) Single Portal with more administration built in for access.
I was leaning towards Separate Portals (but hadn't thought of the Portal ID as an identifier), as that would allow me to fully utilise DNN's user management features. Any updates that I installed (via Host) would propagate to the sites, so updating the actual site was not an issue.
My main questions/concerns are:
1.) When a database gets large, or really at any stage, what is the possibility of "leakage" providing the queries are written correctly.
2.) What kind of slow down would there be if the Portal ID or some other key was included in the query for every query.
3.) Besides needing to update the databases separately, what other issues do you see with using separate databases for each church?
4.) (And I don't know if this a programming or DNN forum question) Do you know a (easy) way to direct certain portals to their respective databases without having to save the data in web.config? I'm concerned that every time a call is made to the database, that the connection string will have to be gotten each time (extra database call), unless I save it in something like the session state, which could be sniffed.
If this enough information?
Cheers
May 18, 2011 at 11:12 am
david-708140 (5/18/2011)
Elliott,Thanks for your reply.
The system is a church management system. This is the description from by business plan: The system is a web-based Software as a Service (SaaS) platform which assists churches with managing their contacts with their congregation and community. (Australian market only)
Basically, they can record contact details about their congregation and community members with whom they interact. Also, if there is some pastoral care (counselling/support) issues that needed to be recorded, they would be also. Other personal information such a pray requests and information on children and compliance with Australia's working with children checks would also be recorded.
My research doesn't show any particular requirements, which is one reason Google hasn't released Google Health for Australia yet. My lawyer said to just make sure that I have a policy in place on how data is handled. I was considering looking at using the HIPPA regulations, just to have a benchmark to aim for. (I'm actually a US citizen living abroad.)
As far as access separation, I was considering two options:
1.) Separate Portals
2.) Single Portal with more administration built in for access.
I was leaning towards Separate Portals (but hadn't thought of the Portal ID as an identifier), as that would allow me to fully utilise DNN's user management features. Any updates that I installed (via Host) would propagate to the sites, so updating the actual site was not an issue.
My main questions/concerns are:
1.) When a database gets large, or really at any stage, what is the possibility of "leakage" providing the queries are written correctly.
2.) What kind of slow down would there be if the Portal ID or some other key was included in the query for every query.
3.) Besides needing to update the databases separately, what other issues do you see with using separate databases for each church?
4.) (And I don't know if this a programming or DNN forum question) Do you know a (easy) way to direct certain portals to their respective databases without having to save the data in web.config? I'm concerned that every time a call is made to the database, that the connection string will have to be gotten each time (extra database call), unless I save it in something like the session state, which could be sniffed.
If this enough information?
Cheers
I would probably go for seperate portals, any off the shelf stuff you buy should be portal aware. PortalId is used HEAVILY within the base code. I would think it wouldn't be all that much of an issue on performance. Seperate databases require additional administration. Last I knew a particular install or set of portals runs fully from one database. I have not seen each portal broken out into its own. Maybe thats just me, if you have seen docs on it I would love to hear. Spillage could be anything from users to prayer requests. Although SOX compliance is not required I would probably look at the data that might be stored and ask myself whether if the whole list was exposed whether this particular field of data would cause embarassment or generate undue risk to the person it applies to. If it does I would consider encryption on that column. You also might do a hybrid kind of thing.. For a big congregation you might provision them out into an entirely seperate install while keeping smaller congregations in a combined one. You could also offer physical seperation as an add-on that is paid for. Not sure whether this is a for-profit thing.
CEWII
May 18, 2011 at 5:06 pm
I would probably go for seperate portals, any off the shelf stuff you buy should be portal aware. PortalId is used HEAVILY within the base code. I would think it wouldn't be all that much of an issue on performance. Seperate databases require additional administration. Last I knew a particular install or set of portals runs fully from one database. I have not seen each portal broken out into its own. Maybe thats just me, if you have seen docs on it I would love to hear. Spillage could be anything from users to prayer requests. Although SOX compliance is not required I would probably look at the data that might be stored and ask myself whether if the whole list was exposed whether this particular field of data would cause embarassment or generate undue risk to the person it applies to. If it does I would consider encryption on that column. You also might do a hybrid kind of thing.. For a big congregation you might provision them out into an entirely seperate install while keeping smaller congregations in a combined one. You could also offer physical seperation as an add-on that is paid for. Not sure whether this is a for-profit thing.
CEWII
Elliot,
Great ideas. I like the separate database as an add-on. I will also look into the encryption of the notes column (which can be marked private by the person entering the information).
Also, you are correct that multiple portals do run from a single database. I don't know of any instances where there is a separate database for each portal, as that would circumvent part of the purpose of DNN.
Cheers.
May 18, 2011 at 5:09 pm
Would it be practical for each client to use a different SQL login/user to access their data in separate databases? If all queries from a particular client portal use the same login, and that login has access to that client's database only, the chances of accidental disclosure of another clients' data is greatly reduced.
Another advantage of using separate databases is that when a client leaves, their database can easily be removed from the server and the drive space recovered.
May 18, 2011 at 5:15 pm
dan-572483 (5/18/2011)
Would it be practical for each client to use a different SQL login/user to access their data in separate databases? If all queries from a particular client portal use the same login, and that login has access to that client's database only, the chances of accidental disclosure of another clients' data is greatly reduced.Another advantage of using separate databases is that when a client leaves, their database can easily be removed from the server and the drive space recovered.
Each user wouldn't have their own SQL database login, but the program would be controlling the login (that's getting into the programming side anyway).
The two points you make are the ones making me lean in the direction of separate databases for the specialized programming, and letting DNN handle the user accounts and portal structures.
May 18, 2011 at 8:21 pm
Also, DNN doesn't work this way. A single user is configured from the IIS server to the database. I'm more concerned about home built modules respecting portal identification. The DNN provided modules are generally pretty solid.
I host my major business portals all by themselves in a single database all minor portals I try to host in a combined DB..
CEWII
May 18, 2011 at 8:41 pm
Elliot,
Thanks. I'm thinking I'll go the same way.
Cheers.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply