Is it OK to store all personal details in AspNetUsers table when using ASP.NET Identity?

David Anderson 206

I have just added user authentication to an existing Web Forms application using ASP.NET Identity. During this process, I learned how to add additional columns to the AspNetUsers table (for RegistrationDate and LastLoginTime).

The only personal details I am currently storing in that table are UserName and Email. Would there be any disadvantages to adding all other personal information, such as postal address, telephone no, mobile no, etc, or is that data better stored in a separate table that is unconnected with the Identity class?

One advantage I can see for a separate Users table would be a UserID that was a simple integer rather than the Id of AspNetUsers (e.g. db44251d-c17e-4dc1-95f8-d1dd7b9a26d8).

AgaveJoe 26,181 Reputation points

2021-09-04T11:56:25.297+00:00

Contact information is commonly stored in a separate table due to standard database normalization. This is especially true if you application stores home and work address or phone numbers; a one-to-many relationship.

One advantage I can see for a separate Users table would be a UserID that was a simple integer rather than the Id of AspNetUsers (e.g. db44251d-c17e-4dc1-95f8-d1dd7b9a26d8).

Well no. The new table should contain the AspNetUser.Id value. This maps the AspNetUser record to the new table record(s).
David Anderson 206 Reputation points

2021-09-04T12:46:15.23+00:00
Hi Joe,
Thanks for rapid response.

Some contact data is always going to be stored in AspNetUsers, e.g. Email. It also contains a PhoneNumber column, though that is probably only necessary if you are using it for two-factor authentication (which I'm not using). If no other personal data requires a one-to-many relationship (as I don't think it does in my case), is there still an advantage in putting it into a separate Users table?

EDIT: Either AspNetUsers or a new Users table would still have to have a one-to-many relationship with a Salutions table (Mr, Mrs, etc) and a Payments table.

I take your point about using the AspNetUser.Id value as the link to a Users table. My comment was prompted by my past experience of using the Membership Provider.

Many years ago I created a big ASP.NET Web Site Application for an annual international photographic competition. This site used the Membership Provider. In that setup, Email was initially stored in the aspnet_Membership table and the Username was stored in aspnet_Users. For convenience, I duplicated the Email and UserName columns in an Entrants table, which stored all other personal details. The Entrants table was linked to aspnet_Users using UserName, so I seldom had any need to use any of the complex PKs in Membership Provider.

2 answers

AgaveJoe 26,181 Reputation points

2021-09-04T13:57:51.68+00:00

EDIT: Either AspNetUsers or a new Users table would still have to have a one-to-many relationship with a Salutations table (Mr, Mrs, etc) and a Payments table.

I don't know your application requirements and can only make general recommendations based on past experience. If you do not need a one to many relationship then storing the user information in the AspNetUser table is fine.

For example, payments commonly consist of a billing and shipping address. I don't know if shipping applies to your application.
Please sign in to rate this answer.
David Anderson 206 Reputation points

2021-09-04T16:30:15.527+00:00

It's a website for a classical music society (https://southamptonrms.org.uk). My plan is to extend it to provide user authentication as a precursor to adding an IPN-based PayPal facility for payment of membership fees. These payments are thus for a service rather than a product, so no shipping address is required. The billing address will come from either the AspNetUsers or Users table. The Payments table will contain PaymentID, UserID, Amount, PaymentDate, etc.

I remain a little confused about your one-to-many relationship comments. Whichever table is used to store my user details will have to have a one-to-many relationship with Salutations and Payments. If that doesn't prevent me from using AspNetUsers then what other form of one-to-many relationship would cause a problem?

AgaveJoe 26,181 Reputation points

2021-09-06T13:55:14.817+00:00

A one-to-many relationship is when one user (or record) can have many attributes like a home phone, cell, and work. Database normalization guidelines recommend storing this type of information in a separate table rather than creating three phone number columns in a single table.

You should read the Database normalization link so you can make an informed decision. Otherwise, the community is forced to ask clarifying questions since we do not have your application requirements.

David Anderson 206 Reputation points

2021-09-06T14:42:37.64+00:00

I've been using SQL Server since 2009, so I'm well aware of one-to-many relationships. On the other hand, it's possible that I may not be implementing Dr Codd's rules as strictly as he might have preferred.

For example, I have always put more than one phone number column in a single table and that's never caused me any problem. Such numbers are almost always unique to each individual in the communities served by my web sites. However, I have always used a separate Salutations table, as all users share a very small number of salutation options.

What I was asking you to clarify was your earlier comment that "If you do not need a one to many relationship then storing the user information in the AspNetUser table is fine". Are you saying that because I want to make a one-to-many relationship with a Salutations table it would not be suitable to store all my user details in AspNetUsers? If so, what problem would that cause?

AgaveJoe 26,181 Reputation points

2021-09-06T15:02:03.01+00:00

A salutation table is a look up table not a one-to-many relationship. For example, it is unlikely a Mr. is also a Mrs.

What I was asking you to clarify was your earlier comment that "If you do not need a one to many relationship then storing the user information in the AspNetUser table is fine".

The concept is very straight forward. If the design has a one-to-many relationship, then another table is required. You are the only one that knows the answer.

David Anderson 206 Reputation points

2021-09-06T15:46:09.093+00:00

My mistake. Salutations does indeed not provide a one-to-many relationship. However, Payments, the other table I mentioned earlier, is such an animal. One user could be associated with multiple payments.

I'm happy to take your advice on this topic, but just out of interest, what exactly would go wrong if I used AspNetUsers for all my user-specific data and linked that to Payments?

AgaveJoe 26,181 Reputation points

2021-09-06T20:21:02.623+00:00

I'm happy to take your advice on this topic, but just out of interest, what exactly would go wrong if I used AspNetUsers for all my user-specific data and linked that to Payments?

This was discussed above. The payment table will have an ID column that contains the AspNetUser ID that made the purchase. Otherwise, how will you know who made the purchase?

David Anderson 206 Reputation points

2021-09-06T21:32:24.843+00:00

I think we must be talking at cross-purposes. The Payments table would obviously have to contain a UserId that links to whatever table is used for my user-related data (i.e. AspNetUsers or a separate Users table).

The thing I'm struggling to understand is your very first response to this thread, where you said "Contact information is commonly stored in a separate table due to standard database normalization. This is especially true if your application stores home and work address or phone numbers; a one-to-many relationship".

Given that I do wish to make a one-to-many link to Payments, my conclusion from the above statement is that there would be some unspecified problem if I stored all my user data in AspNetUsers. That is the problem I am asking you to clarify.
Sign in to comment
David Anderson 206 Reputation points

2021-09-05T16:43:55.297+00:00

As an additional question, is there any reason why I shouldn't change the name of the Id column (the PK) in the AspNetUsers table? I would prefer to have a more self-explanatory name of UserId.

EDIT: I've just looked more closely at the PK/FK naming convention for the ASP.NET Identity tables. Where a table has a single PK (as in AspNetUsers and AspNetRoles), it is always called Id, but when this is used as a FK in another table it is given a more informative name that reflects its source, i.e. UserId and RoleId. Simply following that convention resolves my issue.
Please sign in to rate this answer.
Lan Huang-MSFT 25,231 Reputation points Microsoft Vendor

2021-09-06T05:16:33.377+00:00

Hi @David Anderson ,

what other form of one-to-many relationship would cause a problem?

What are the specific problems you encountered, and what other forms of one-to-many relationship refer to?
You can start a name that is more specific or can see the purpose of this table according to the camel case format, such as AspUserId.
Best regards,
Lan Huang

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our  documentation  to enable e-mail notifications if you want to receive the related email notification for this thread.

David Anderson 206 Reputation points

2021-09-06T08:06:31.97+00:00

Hi LanHuang,
I don't yet have any one-to-many relationship problems. If you read my original post again, you will see that I am a very new user of ASP.NET Identity and was simply asking if the AspNetUsers table was a good place to store all my user-related data.

The first response from AgaveJoe stated "Contact information is commonly stored in a separate table due to standard database normalization. This is especially true if you application stores home and work address or phone numbers; a one-to-many relationship".

I was asking AgaveJoe to clarify that statement.

Lan Huang-MSFT 25,231 Reputation points Microsoft Vendor

2021-09-06T08:24:46.177+00:00

Hi @David Anderson ,
Do you have any other questions?
If so, I suggest you create a new post.

Best regards,
Lan Huang
Sign in to comment