Can i add new custom tables inside my aspnet database inside asp.net MVC-5 project

Question

I have the following:-

1. Visual Studio 2013.
2. i created a new asp.net MVC-5 web project.
3. the project is using asp.net identity 2.2.
4. for the authentication method i chose "Individual user accounts"
5. this process created a new database named aspnet-OurProjectNanme-number
6. inside the automatically generated database, i have a table named AspNetUSers which store the user info.

now i am working on building an ERP system. and inside the ERP system i want to add the following:-

1. a table named "Asset" to store the asset info.
2. the "Asset" table will have 2 columns named "CreatedBy" + "ModifiedBy" which should store the userId who created and modified the asset item.

now i am not sure how i need to achieve this? as i need to add a foreign key between my custom table "Asset" and the "AspNetUsers" table which have been created automatically.. so can i add my custom table "Asset" inside the automatically generated database, and build the foreign key between the Asset.CreatedBy and AspNetUsers.Id ??

• if the answer is Yes then can this relation break in the future if we want to upgrade our aspnet identity version ? as upgrading the identity might result in creating new tables or renaming existing ones etc.. which might break the relation between the Asset table and the AspNetUsers table?

• If the answer is No (i should not add custom tables inside the automatically generated database ) then how i can build the foreign key ?? and where i need to add the Asset table in this case??

Answer 1

The most common approach to what you want to do is simply to add your additional model as a DbSet in your ApplicationDbContext.

public class Asset
{
      public string CreatedBy { get; set; }

      public string UserId { get; set; }

      public ApplicationUser User { get; set; }
}

public class ApplicationUser : IdentityUser
{
     public IList<Asset> Assets { get; set; }
}

public class ApplicationDbCotext : IdentityDbContext<ApplicationUser>
{
     public DbSet<Asset> Assets { get; set; }
}

As I mentioned this is the most common approach as updating the Identity packages should have no impacting affects on your schema. That said you should always test updates before pushing to production.

UPDATE: Note that when you're working with One to Many relationship's you will see in our Asset model a property for the User Id foreign key as well as the User object. Because of the relationship we are then able to create a List<Asset> in our User to complete the One to Many relationship. This will then allow us to directly query Assets belonging to a User.

As for Code First vs Database First the difference really comes down to how you define the mapping between Entity Framework and the Database.

As I mentioned below there is no one size fits all answer to should you separate the Identity context from your business context, or should you separate them into separate databases. The reality is that only you can answer that question for your needs. It is far more common to have all of the data in a single database. That said, there is something to be said for the security of having identifying information about a user such as their name, email and password hash separated from information like their address or payment information. The trade off is that you can find yourself trying to maintain objects that are supposed to be tied together but are only loosely related because they reside in different databases. Also you would then need to make sure you're using different users/passwords to connect to the different databases, and it's better to have the databases on different servers because if the server gets compromised you went through the entire exercise for nothing. The trade off to get the theoretical security ends up being so impractical with there consistently being another thing you have to do, that you end up seeing everything in one database where you can focus all of your hardening efforts.

Both the ApplicationDbContext and ApplicationUser objects should typically be created for you when you File -> New a project with Individual Authentication. You can add as many properties and relationships to your User as you require.