Dealing with data in multiple databases and Entity Framework

Question

I've got the following setup (obviously the diagram shows a simplified version)

enter image description here

I wanted to eliminate duplicating common data between databases, in my example, this is the customer table in DB A. In my other apps, I just store the CustomerGuid. I'm thinking of adding a constraint to make sure the CustomerGuid is valid.

So far so good.(Or maybe not)

The problem is when I am in my application layer. I need to create two contexts and then join in memory when I need to retrieve a customer's name and address.

Is this the best approach? It seemed better than replicating the tables in each DB but now I'm not so sure.

My options are as follows:

Create views to join in DBs B,C (I'm leaning towards this one, but the problem is I can't form a full text index on views that draw from tables in multiple databases)
Keep dealing with multiple EDMX and join in memory when I need to. (Full text search is also a PITA)
Replicate data in new tables in each database (seems icky)
Linked servers? How does EF deal with this?

I don't have a straightforward question, (so perhaps I will be down-voted to oblivion) but I wanted to elicit some feedback and suggestions as to which approach is best and/or perhaps an approach I haven't thought of. What is the best practiced recognized?

Out of curiosity, why do you have these in separate DBs? Are there tables you aren't showing us or is it just those tables in the DBs that you listed? — Dan Drews
– Dan Drews, Commented Nov 15, 2013 at 21:14
There are tables I'm not showing, I just simplified the diagram for the sake of clarity. — Matt
– Matt, Commented Nov 15, 2013 at 22:25
Linked tables would be my choice for this...what database are you using? MSSQL? — Twelfth
– Twelfth, Commented Nov 15, 2013 at 22:47
Yes, sorry..linked servers not tables. MSSQL 2008 handles them pretty well. Depending on what you're facing here, it might be better to create a dedicated datawarehouse server and some ETL packages to grab the data and move it to a centralized server instead. Tons of options, just a matter of choosing which best suites your requirements. — Twelfth
– Twelfth, Commented Nov 15, 2013 at 23:27

qujck · Accepted Answer · 2013-11-15 23:50:41Z

1

You should try to "let go" of the old OO ways when designing with modern solutions such as DDD and ORMs. There's really no issue with having data in more than one place as long as you can design to ensure the data will normally be the same.

Read this article on Julie Lerman having the same dilemma.

Linked servers are very cool but should not be an up front dependency.

answered Nov 15, 2013 at 23:50

qujck

14.6k4 gold badges48 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dealing with data in multiple databases and Entity Framework

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related