Good architecture for user information on separate databases?
- by James P. Wright
I need to write an API to connect to an existing SQL database. The API will be written in ASP.Net MVC3.
The slight problem is that with existing users of the system, they may have a username on multiple databases.
Each company using the product gets a brand new instance of the database, but over the years (the system has been running for 10 years) there are quite a few users (hundreds) who have multiple usernames across multiple "companies" (things got fragmented obviously and sometimes a single Company has 5 "projects" that each have their own database).
Long story short, I need to be able to have a single unified user login that will allow existing users to access their information across all their projects.
The only thing I can think is storing a bunch of connection strings, but that feels like a really bad idea.
I'll have a new Database that will hold the "unified user" information...can anyone suggest a solid system architecture that can handle a setup like this?