Can I add new user tables inside my aspnet database in asp.net MVC-5 project

Question

I have the following: -

• Visual Studio 2013.
• I created a new asp.net MVC-5 web project.
• The project uses the identifier asp.net 2.2.
• for the authentication method, I selected "Individual user accounts"
• this process created a new database called aspnet-OurProjectNanme-number
• inside an automatically created database, I have a table called AspNetUSers that stores information about the user.

Now I am working on creating an ERP system. and inside the ERP system I want to add the following: -

• a table called "Asset" to store asset information.
• the "Asset" table will contain 2 columns named " CreatedBy " + " ModifiedBy ", which should store the userId that created and changed the resource element.

Now I'm not sure how I need to do this? since I need to add a foreign key between my custom "Asset" table and the "AspNetUsers" table, which were created automatically. so I can add my own “Asset” table inside an automatically generated database and create a foreign key between Asset.CreatedBy and AspNetUsers.Id ?

• If the answer is Yes , can this relationship break in the future if we want to update our version of aspnet authentication? since updating the identifier can lead to the creation of new tables or renaming of existing ones, etc., what could break the connection between the asset table and the AspNetUsers table?

• If the answer is No (I should not add user tables inside an automatically generated database), then how can I build a foreign key? and where do I need to add an asset table in this case?

Answer 1

- Update -

The answer is growing and growing, as well as discussion . I think I showed all kinds of options that might not have helped to make it understandable. So here is the summary. Read the full answer and discussion for clarification.

Out of the box, you have two contexts: personality and business. They are untied, so you can change your security without interfering with your business. Thus, increasing security will not violate your application or other models. Since contexts are separate, changes to one of them will not affect the others.

As a side element: you do not have direct access to AspNet identity tables. Deploy the UserManager and use the available manager methods to perform the actions.

Now we are talking about logic, where should the information be stored? As a simple rule, ask yourself: is this part of security or business?

In both contexts, you have users. For your requirement, this is a logical ratio of 1: 1. But they are truly separate. You can create people without providing a login or delete a login without deleting a user (s), for example. for historical reasons.

All you want is to find all the information for the current user. So all you need is People.Id.

Without the need to modify IdentityUser, you can create a 1: 1 ratio by simply overriding AspNetUser.Id.

 var appUser = new IdentityUser { UserName = model.Email, Email = model.Email, Id = Convert.ToString(People.Id) }; var identityResult = await userManager.CreateAsync(appUser, model.Password); 

You do not need an identification context for your company. All you need is People.Id. The identification context is used only when tokens are issued and users are created / modified.

To get the id, use something like this:

  var peopleId = int.Parse(Request.User.Identity.GetUserId()); 

Now you can request your business model using Id.

When registering, extend the View and ViewModel with the People information you want to keep. This will allow you to add users and AspNetUser at the same time. Although this is not a single transaction. But I think it is very unlikely that creating one of them will fail if you complete the checks first.

You can check the username and password (use the methods in the UserManager) and check the ModelState of the viewmodel before creating the user. Use attributes to force required fields.

- Original answer -

In order not to repeat, read my answer here .

In short, keep your identity and business separate. Just in case, personal logic is deleted from one database, for example, when implementing IdentityServer.

It seems you have business information in AspNetUser. If so, create a Person table and move the information to this table. Regarding this table in your model. In Person Person, you can add a link to AspNetUser.

- update -

I think you understood correctly, but I will just add details to this answer.

In most cases, all tables are defined in one database. But this does not mean that they are all part of the same model. There may be several contexts. In this case, one for Identity and one (or more) for Business.

Now why separate the two? The most important difference between the Business Model and Identity is that Identity tables do not have to be called directly. We use the Owin context to invoke the UserManager / RoleManager.

This is why we cannot add these tables to the business model. Things can be changed so that they are not safe. Also, we do not want the business to have any knowledge about authorization. It does not matter how this is done if the user is identified and authorized.

You can also use OpenId and requirements-based authorization. In this case, the information should not be available in the database.

The idea is to create a 1: 1 relationship for the AspNetUsers identifier table and the People business table. There may be some redundancy, such as an email name or (username). But it's not a problem. The People table should contain all the information you want to use in your business model. And business tables should only apply to people, not AspNetUsers.

Now about the connection between AspNetUsers and People. There are four options:

• Set People.Id = AspNetUser.Id. Note that AspNetUser.Id does not have to be a GUID. You can add your own value as a key.

• Set AspNetUser.Id = People.Id.

• Add AspNetUserId column for people. No changes to Identity are required. You can also add people to the identity model, but I don’t think you can create both records in the same transaction. You can use User.Identity.GetId () to get AspNetUser.Id. However, you may ask yourself if the business should know about this information.

• Add PeopleId column to AspNetUsers. You will need to extend IdentityUser to add PeopleId. The advantage is that you do not need the AspNetUser identifier, but you can use the actual people identifier. When using OpenId or claims, you can get People.Id from claims, and you don’t have to add AspNetUser.Id to the business. If you wish, you can add people to the model and as a navigation property of the extended IdentityUser. When creating a user, you can do this in one transaction.

If you create a user in separate contexts, you will have to handle rollbacks yourself. But before adding an entry to People, you can already check if AspNetUser can be added: it has a valid name / email address and password.

Since your business model refers to the People table, you can query all assets and join the People table for more information. Or you can get all the assets for the current user.

o yes, there are two contexts. An identification model containing AspNet ... + tables optionally People. And a business model containing all the ERP + Asset + People tables.

First, you can first use the code for the identity structure 2 and the database for the business model.

I hope this helps. If not, continue chatting.

- update -

The answer focused on the separation of domains: identity and business. That is why I did not discuss one possible alternative regarding the AspNetUsers table.

The two models are database representations, which means that the database does not have to be accurate. You can display tables and fields as you wish if they do not violate the database logic.

Since AspNetusers and People have a 1: 1 ratio, and when both tables are in the same database, you can also combine them into an AspNetUsers table. You can also add relationships to the AspNetUsers table, although you can add an additional Id (int) column instead of using the current identifier (row).

This does not mean that the People class can be dropped, except that we need to change the display of the table: AspNetUsers.

Example:

 [Table("AspNetUsers")] public class People { [Required] [StringLength(128)] public string Id { get; set; } public string FirstName { get; set; } public string LastName { get; set; } } 

As you can see, sensitive fields are not displayed. We need, however, the field Id. Now you can read and update the displayed fields.

You do not need to extend IdentityUser. You can add AspNetUser and then update the fields using People in a different context. But if you want to add a user to a single transaction, it may be easier to extend IdentityUser (make sure you define new fields in People and ApplicationUser):

 public class ApplicationUser : IdentityUser { public string FirstName { get; set; } public string LastName { get; set; } } 

There are several advantages:

• There is only one transaction to add a user.
• You cannot open sensitive fields because they are not displayed in People.
• You cannot add people to the database, as some required fields are not displayed in People.

Please note that this may not work for all types of models (first code / first database + migration).

Answer 2

The most common approach to what you want to do is simply add your optional model as a DbSet to 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 said, this is the most common approach, since updating Identity packages should not affect your schema. However, you should always test for updates before starting production.

UPDATE: Please note that when you work with relationships from one to large, you will see in our Asset model a property for the foreign key User Id, as well as for the User object. Due to the relationship, we can create a List<Asset> in our user to complete the One-to-Many relationship. This will allow us to directly request Assets owned by the User.

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

As I mentioned below, no size fits all answers if you separate the Identity context from your business context or separate them into separate databases. The reality is that only you can answer this question for your needs. More often, all the data in one database is used. However, there is something to be said about the security of identifying user information, such as their name, email address and password, separated from information such as their address or billing information. The trade-off is that you can try to maintain objects that should be related to each other, but they are loosely connected to each other because they are in different databases. You will also need to make sure that you use different users / passwords to connect to different databases, and it is better to have databases on different servers, because if the server becomes compromised, you went through the whole exercise in vain. The compromise to get theoretical security turns out to be so impractical that one more thing is constantly required that you have to do in order to ultimately see everything in one database, where you can focus all your consolidation efforts.

Both ApplicationDbContext and ApplicationUser objects are usually created for you when you File -> New execute a project with individual authentication. You can add as many properties and relationships to your user as you need.