Architecture – Multi-Project Multi-Database Intranet Design Suggestions

application-designArchitectureasp.netcode-reusesql server

Here is the situation I have inherited:

  1. We have approximately 10 websites (Asp.net web forms) that each have their own database.
  2. Each of these databases houses some site specific data, and each has some form of a user table that will map to what is called a User Service.
  3. The User Service is just another website that has a database with one table (User) that have about 3 different unique identifiers for a user: a UserId guid, an internal desktop application id (third party app, needed to map back to it), and a username.
  4. On all the different websites the databases have the User table, sometimes called Person, or Student, or whatever else, and they are inconsistent in which UserService.User.[IdentifyingColumn] they map to.

The Problem:

It is getting to a point where these application are now needing to share some data with each other, and I see that need increasing as the departments are streamlined even more. With this current set up, it is a lot of hoop jumping to get the right UserId column to match to the right UserId column in the UserService.

Confused yet? Me too..

The Question:

How to approach correcting the issue? I have a few different ideas, some I like more than others, but in order to implement them I need some solid benefits I can bring up to the head of IT, as well as justify the programming time taken for the re-write.

Idea 1: Create a true intranet application. One web application with one database. This way they all live in the same project, and sharing data between the various departments is very simple and quick.

Idea 2: Create a "Master Key" identifying field that will be the end all to identify a user. Make this key available on each database and so there will not be any odd mapping and the various sites can talk to each other. To me this one solves one problem, mapping each db to the next one. It is still very fragmented and there is very little code re-use.

Idea 3: Work with it in the current form trying to continue the present fashion of many small databases and sites.

Which of the above options would you suggest, and why?

Best Answer

Single Sign On is an excellent idea. Just so you know, you don't necessarily need to use a specific technology, such as Active Directory, WIF or OAuth. You can implement it with just ASP.NET forms auth. Here's how you do it:

Selling it to the head of IT

  1. Don't make it just about developer happiness, there's a significant user happiness benefit of SSO.
  2. Take your current workload and project the time required to keep mucking with an imperfectly architected system over a year. Compare that with the time required to do a re-write. Most of the time, if it breaks even after a year, it's a good trade.

Deciding on How

  1. Figure out the most common way users are identified, (such as AD name, or email address) and try to centralize on that - it should reduce the scope of the re-write.
  2. Once you have the best key defined, pick a technology or method that leverages that.

Random Tips from my experience

  1. I've had the least friction with OAuth. Mainly because we used google apps, so everyone already had a corporate google account.
  2. Windows Authentication has problems depending on if it's set up with NTLM or Kerberos - I had a particular problem with NTLM over a VPN.
  3. ADFS was just too much hassle to get working correctly for anyone outside of our domain, it really wasn't worth it.

To keep your application maintainable, I would make the configuration as simple as possible. To your question about the separate databases, if they don't buy you anything by being separate, reduce the complexity and merge them. Since they are an intranet, I'm assuming they won't ever be deployed separately.

Related Topic