Wednesday, 4 July 2007

ASPNETDB.MDF?

One of the first things I tried to get my head around while learning ASP.NET 2.0 recently was the user and membership system. Its a nice idea - Visual Studio can go off and create a database for you with all the tables ready to go. It certainly saves a lot of design and implementation time by providing a solution for a common problem.

The only strange thing is where it puts this data. As far as I can tell, it never asks you where you want to store the data. It simply creates a new SQL Express file called ASPNETDB.MDF. In fact, it uses a connection string that is hidden away in your machine.config file, which I found quite confusing at first, as I couldn't work out how my web application knew how to connect to this database.

The trouble with this is obvious. If you already have a database, then you now have two databases. This not only is a problem for those whose web hosting provider charges them for multiple databases, but more seriously doesn't allow you to have a foreign key in one of your data tables pointing at the user table.

Now you might say that I am making a fuss about nothing. Surely I can just move these tables into my existing database. And I probably will. But I noticed that the SubSonic starter site has its data still separated into two databases. Why is this? The CMS database simply stores "user name" and has no hard link to the ASPNETDB database. Of course, you could write some code to look up user details (such as display name, email etc) from the user name, but you have lost the ability to get all your data with one request. What's worse, if a user is allowed to change their user name, all relationships will be broken.

So my question is, why are people doing this? Is there any benefit to having the databases separate? Or is it just because that's what Visual Studio does by default and it is a pain to change?

(not that I'm expecting an answer. I don't think this blog actually has any readers yet!)

Post a Comment