Best Solution for Designing MultiDB Apps?
-
We have to develop a suite of applications (mostly asp.net) based upon sql server which will share the same user base ("table"). Eg. first name, last name, email address, login, password. Some of the apps will use special properties assigned to the user, eg. app1 uses a field called "canrequestorder". The other apps do not use this information - so it is unique to this app1. The user table shall also indicate if the user is able to use the application (eg. a field called "canuseapp1").
On the target system there can be installed one or more apps from the suite. Eg. only app1 or app1, app2 and app3.
I am currently investigating the possible solutions of how and where to place and access the shared user base. Depends on the usage.
Some of my current cosiderations are:
1) Create a db for each app and one "master" db which holds the common shared information and the field which indicates if one of the apps can be used. Create a user table for the special properties in each app db linked by a unique id with the master user table. Refer to the master user table (eg. for displaying the user name or send an email) by using the full qualified name. (Currently unsure how, eg. by a "local" view which simply selects from the "remote" table, a function or else.)
2) Create a db for each app and one "master" db which holds the common shared information and the field which indicates if one of the apps can be used. Create a user table for the special properties in each app db and include the same fields (except the "canuseappX" fields) from the master user table and synchronize regularily eg. ba script.
I hope I have placed all necessary information here so you can understand my scenario.
What do you think?
Thanks for help!
Regards,
Manuel
-
This was removed by the editor as SPAM
-
i'm not sure what you're trying to acheive here that can't be done with the existing functions and tables?
sureley assiging permissions to a database and then using sql servers native permissions will allow you to controll access to the apps? just query the permission and if there is none then don't start the app.
if it's a case of holding address details then i'd create a new database for contact details, but still leave the application permission handling to the already built in features
MVDBA
-
Mike,
thanks for your reply. The problem is that we can not use integrated or sql logins. This is why we have to implement our own user and permission management...
There are also some other common tables which have to be shared between the apps. So it is like a master db for the application suite.
Another idea might be to build a single db for all apps. But this might become difficult to handle in the future when there are different versions of the apps because we would have to maintain a lot of sql scripts to adjust the db depending on the app(s) versions the customers have then...
Any other ideas?
Regards,
Manuel
-
Nothing wrong with creating your own system of user permissions. Can't say if the multi db idea is good or not, I think really depends on your environment, plans, etc.
We do use a similar set up at work. Logins, permissions, and a lot of common data are stored in a main db, then each client has their own db. We try to keep the schema stablized across all clients at a base level, occasionally we have to extend to solve some problem the client presents. The upside is that each client feels better than their data is partitioned, the downside is that when we have to apply changes we have to do so multiple times. The obvious alternative is to keep the data all in one, add an additional column to one or more tables with a clientid. In practice I think the pro's outway the con's for us easily.
-
Andy,
many thanks for your reply. I am evaluating the pros and cons for a few weeks now and I also found some of the problems you mentioned.
Besides for the "master" db scenario I am a bit unsure about the following:
- What about the performance since a lot of the tables in the app dbs will have constraints with tables in the master dbs.
- What about "3rd party" Reporting Utilities like Reporting Services or Crystal Reports? Designing the reports will result in a lot of cross db access (ok, we could provide some views in the app dbs which will simply link to the tables in the master db so that only the app db would be necessary as datasource for creating the reports which rely on tables in both dbs).
Nevertheless many thanks again. Its very helpful to get some impressions and experiences from others!
Regards,
Manuel
-
We put views into the db's that reference the main db, that way it appears to be in one place, handy both for reporting and programming against. Haven't noticed any perf issues as a result.
-
Know this is a late reply, but we do pretty much the same thing and have for a while. All logins and basic information is stored in one location. All other apps query from there to determine login properties and permissions. We also store NT login information to lookup those usernames as well - easy passthrough from the app to the database to determine everything and the user doesn't have to remember yet another username/password combination. 🙂
It works well, especially once we got all of the systems integrated. That was probably the hardest part for us as we'd had some systems developed outside of our control that had their own login/lookup methods. If you're starting from scratch, you should find that the maintenance of a "master" database works well for you.
-Pete
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply