Different Tables or different Databases for Business Data and Identity

Question

I read through Apress - Pro Asp.Net MVC 5 and the free chapters of the Identity Framework and now, I want to create a small sample application with some data and Identity. Later I want to make a test deployment to Windows Azure. Now, should I create one single Database for this Application, containing all Data (Products, whatsoever, IdentityData (User-Accounts, Oauth Linkings...)) or would it be better to create two Databases? I know, If I'd create two, I would be able to use the same Identity-Data for other MVC Applications, but is there some kind of best practice for MVC?

Accepted Answer

There's no "best practice", per se, in this area. It depends on the needs of your individual application. What I can tell you is that if you choose to use multiple database, you'll end up with a somewhat fractured application. That sounds like a bad thing, but remember this is a valid choice in some scenarios. What I mean by that is simply that if you were to separate Identity from the rest of your application, requiring two databases and two contexts, there's no way, then, to relate your `ApplicationUser` with any other object in your application.

For example, let's say you creating a reviews site. `Review` would be a class in your application context, and `ApplicationUser` would of course be a class in your Identity context. You could never do something like:

```
public class Review
{
    ...

public virtual ApplicationUser ReviewedBy { get; set; }
}
```

That would typically result in a foreign key being created on the reviews table, pointing to a row in your users table. However, since these two tables are in separate databases, that's not possible. In fact, if you were to do something like this, Entity Framework would realize this problem, and actually attach `ApplicationUser` to your application context and attempt to generate a table for it in your application's database.

What you could do, though, is simply store the id of the user:

```
public string ReviewedById { get; set; }
```

But, again, this wouldn't be a foreign key. If you needed the user instance, you'd have to perform a two step process:

```
var review = appContext.Reviews.Find(reviewId);
var user = indentityContext.Users.Find(review.ReviewedById);
```

Generally speaking, it's better to keep all your application data together, including things like Identity. However, if you can't, or have a business case that precludes that, you can still do pretty much anything you need to do, it just becomes a bit more arduous and results in more queries.

Different Tables or different Databases for Business Data and Identity

Solution

Problem