Best Practice ASP.NET Membership: User tables in the same datastore?

Question

Is it better to extend my business database with the tables of the ASP.NET Membership Security model. Or should I have a different datastore where I only manage Identities and Roles... Basically 1 or 2 databases?

Answer 1

This is quite subjective, but unless those users are going to be using more than one database, then I'd say keep them in the same db.

I would only use a separate database for users and roles if those users and roles were used in more than one database.

So no, I'd never use two. I might however use three.

Answer 2

This can depend on scale. If it's an enterprise solution with different apps sharing one membership source the answer is simple - separate them. There might also be performance reasons why you would want to separate this data from the rest of the app. Arguably these tables do not belong in a data warehouse for example.

The only thing the 2 databases solution doesn't give you is referential integrity. If you extend your membership tables to hold more application specific details about the user, and these tables need to link into the main database then you might want to keep them together. Otherwise you would need some sort of replication job maintaining this for you.

Answer 3

Which database platform are you using? If one that supports schemas within a database, e.g. SQL Server 2008, then you can put your membership tables into their own schema, for neatness. You can also add cross-schema foreign keys if required.