1

I have a question about using form authentication in ASP.net MVC.

I ran aspnet_regsql against the SQL 2008 server database that my MVC 3 application is using. This created a new database in my server. I can now login and secure my controllers.

My question is this: Should I change the database where my application stores its data? Or am I supposed to use two databases, one for security and another for my application?

Thank you for any advice.

gideon
  • 19,329
  • 11
  • 72
  • 113
jason
  • 3,821
  • 10
  • 63
  • 120
  • I think you should leave the aspnet generated database alone. You won't need to touch it and there's no chance of messing it up. – gideon Jan 17 '12 at 02:59
  • thanks, I think I agree. How would you suggest creating many-to-many and one-to-many relationships from the aspnet db and the software db? – jason Jan 17 '12 at 04:12
  • See these two similar questions for some possible choices (bottom line: you're better off keeping them in the same database unless it's really necessary not to): http://stackoverflow.com/questions/5166641/sql-server-foreign-keys-across-database-boundaries-techniques-for-enforcement and http://stackoverflow.com/questions/265921/sql-design-around-lack-of-cross-database-foreign-key-references – patmortech Jan 17 '12 at 05:14

1 Answers1

4

You can do it either way. You can combine the security and application data into a single database, or you can keep them as two separate databases. Which you choose depends upon personal preference and the complexity of your application database. You may wish to keep them separate so that the tables are separated by their function into separate databases. On the other hand, this will make your database maintenance a bit more complicated, since now you will have to back up two databases and, in the case of a disaster, restore two databases instead of only one.

Edit: As you pointed out in your comment, if you split these into two databases and you want to have a foreign key reference from your application database to your authentication database, this can be a problem. Sql Server does not support cross-database foreign key references. If this is a deal-breaker for you, you can either 1) combine both databases into a single database, or 2) look into some of the work-arounds to circumvent this lack of support for cross-database foreign key references. If you choose to go this second route, there is a good stackoverflow post on this issue that you might want to start with.

Community
  • 1
  • 1
Joe Alfano
  • 10,149
  • 6
  • 29
  • 40
  • Thanks for your response. my question, then, is if i use two databases, how would you suggest tying the two together and creating associations? For example, one-to-many or many-to-many? Should I just create another "users" table in the software database? I'm pretty sure I can't create a foreign key from one database table to another database table. – jason Jan 17 '12 at 04:11
  • Hi Jason. You are right, if you split this into to DBs, there is an issue with doing cross-database foreign key references. See the edit to my comment for some work-arounds. Thanks. – Joe Alfano Jan 17 '12 at 15:40
  • Additionally some hosting companies charge more to use two DB's. – RickAndMSFT Jan 18 '12 at 01:06