Entities and multiple databases

Question

Hi,

We've got the following scenario:

Central Database (replicated across multiple servers)
Client Database 1
Client Database 2

The Central db has Users and Roles amongst other things The Client dbs have similar tables to each other but with some fields tweaked - contact, address, etc...

At present, each client db has its own user/role information which is copied from the central db by a scheduled process. I want to retrieve the user/role information directly from the central db instead (bearing in mind tables in the client db make reference to the user entity)

Is this even possible? If not, what's a better approach for having central user configuration across multiple databases?

Answer 1

Does this mean that you have referential integrity between tables?

bearing in mind tables in the client db make reference to the user entity

If yes, as long as you have referential integrity between tables they must be in the same database. That points to your current solution being the best.

If no then linked tables would be the way to go, the tables would appear to be local, but the data would be retrieved from the cental database each time.

You EF4 will also not generate linked tables.

Your other option would to go for a more service orientated architecture, creating a user service connected to a web service. But this is probably a lot of work.