Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Is better to keep our Membership stuff on the DefaultConnection and create another connection (another database) for our data? Or just one database for all?

If I have a MyAppContext and I want migrations for that context, It seems that I cannot have migrations for UserContext (In other words, I can just migrate one context)

So, having two different databases I can migrate or the users (maybe membership migration is weird) or the web data. Or, I can mix the UserContext and MyAppContext in one UserAndAppContext and migrate all in one place, but this mixing also seems weird.

What's the normal way to do this, one or two databases and what should be migrated?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

The 'normal' way is to keep it all in a single database. Its just fewer moving pieces you have to keep track of in your application. You can always move the member list into its own database should the need arise.

The only time I'd separate them from the start is if the long term plan would be for the membership list to reside on a physically separate server - perhaps in support of multiple applications. This would let you assert the code wasnt doing JOINS that would not work in such a setup.

share|improve this answer
    
even if several applications addressed the same members data, wouldn't the performance be better if data was duplicated in the databases of each application? –  superM Dec 13 '12 at 17:11
    
Good to know. Do you have answer for the other questions too? Migrate just user data, mix contexts... –  Jesus Rodriguez Dec 13 '12 at 17:56

I respectfully disagree with this answer. Having different data in different databases on the same server can provide significant advantages:

  • Users can "own" one database and have full control of it, but be highly restricted or even denied access altogether in the other. This is a virtual necessity for a schema governing application security; the account used to validate a user should be able to do no more or less. What they log in with here will determine what they should get access to in the main database.

  • Different databases can have different purposes, different target applications, different performance demands, and thus different schema designs. One schema could be set up to be hit fast and hard by a battery of automated systems that input raw data; narrow, deep, somewhat denormalized tables optimized for pure speed. Then another database could target user applications, and thus have a more normalized structure based on needs for proper organization, hierarchical structure and integrity. A mediator can pipe the data from one into the other, respecting the need for speed on one side and producing the normalized, hierarchical structure needed by the other.

  • Keeping schemas that are targeted by different applications separate keeps those schemas, well, separate. A change needed by one application to its schema thereby doesn't necessarily have to affect another. If you're juggling half a dozen different user applications, each with subtly different needs, not having to make sure one change to one app doesn't break any of the five others is huge.

  • Separate DBs for separate data also allows much easier migration. Say you have three departments of your business in one office building in Chicago, each using an app targeting a DB in a server in the back room. Then, you move your helpdesk services to Abilene, cause it's cheaper. The database needed by the helpdesk app needs to be close to the helpdesk computers geographically. What's easier? Creating a backup, pushing it down onto a new server in Abilene, and pointing a few data replication jobs at the new server over a VPN or leased line? Or, extricating a big chunk of the existing schema, which may have been allowed to become entwined with the schema of other apps (since, you know, the tables are in the same DB, so why not set up the foreign keys, right?), setting up a new database with those tables from scratch, migrating all the data over, then testing all three departments' apps for regression?

  • Separate DBs mean smaller schemas means smaller backups means faster (and more granular) restores.

More moving parts it may be, but that's not always a bad thing. It's only a problem when the parts don't fit together well, or aren't "well-oiled" and don't move in sync, or the entire function of the larger entity depends on this one tiny delicate part. These are things to take in consideration, but are by no means supposed to be a deterrent to a more distributed data model.

share|improve this answer
    
I'll upvote this answer because you explained it very well. But I just wanted a "recipe" for simple apps, with simple I mean just 3-4 users and not more than a few mb of information. A toy web page :) –  Jesus Rodriguez Dec 13 '12 at 23:41
    
No need to have different databases for this case, just put them in different schemas within the same DB (assuming your DB supports such a thing) they can be secured using this almost as well. If not, different databases are good. –  gbjbaanb Apr 8 at 10:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.