A design question around AspNetIdentity

Solarion

Honorary Master
Joined
Nov 14, 2012
Messages
20,666
I have a question for you guys with more experience in what I am trying to build. The Identity tables handles all of your roles, users, logins and what not. You have the option of customizing these tables so for instance AspNetUsers you may have additional FirstName, LastName, Address etc

Say now you want to add an Employee table. I think you can already see where I'm going with this. An Employee table may already have many of those details. Is it a good idea to have an Employee table in addition to the AspnetUser table? Could I simply just use the latter table to hold any details around an Employee and ditch Employee table and perhaps rather have a UserType table?

A User may not always be an employee. He could be a guest or an external accountant. But an Employee will always have to be a User in order to gain access.

Battling to wrap my head around this design.
 

scudsucker

Executive Member
Joined
Oct 16, 2006
Messages
7,961
A User may not always be an employee.
It has been a looooong time since I used .NET Identity, but I think you have answered your own question there.

If Users have several possible subtypes, then create a separate table to differentiate them, keeping only what is common to all Users in the Identity tables.

FWIW I have not seen a lot of customisation been done, generally I think data structures are unique to the use case and customising the MS provided Identity tables complicates things, and can violate the "separation of concerns" principle.
 

Solarion

Honorary Master
Joined
Nov 14, 2012
Messages
20,666
It has been a looooong time since I used .NET Identity, but I think you have answered your own question there.

If Users have several possible subtypes, then create a separate table to differentiate them, keeping only what is common to all Users in the Identity tables.

FWIW I have not seen a lot of customisation been done, generally I think data structures are unique to the use case and customising the MS provided Identity tables complicates things, and can violate the "separation of concerns" principle.

That clears things up a little thanks scud. I would prefer to simply only have the login credentials/roles etc kept there. As you said to start customizing those tables may be violating separation of concerns.
If Users have several possible subtypes, then create a separate table to differentiate them, keeping only what is common to all Users in the Identity tables.

I like this though. Everyone has FirstName, LastName etc. Food for thought. I'll wait and see what some of the other guys say.
 
Last edited:

DA-LION-619

Honorary Master
Joined
Aug 22, 2009
Messages
12,038
This is where things get fun because it's so easy to **** up, it won't even be your fault.

You may start messing around with AspNetUsers if the default constraints are not what you want, like the email address being unique. If you override the underlying properties wrong then you could break the helpers.

EF Core 5 seems stable now compared to upgrading in previous versions which could also break things.
Anyway look at the docs, they cover using a differentiator/discriminator

Basic SQL concepts also help here, selecting what you need, thinking about what relationships are required as that will affect the type of joins that end up being used unless you explicitly tell EF what to do.
 
Last edited:

Johnatan56

Honorary Master
Joined
Aug 23, 2013
Messages
28,853
Would design something along the lines of a login table that only has username, password and their account id. Then another table with roles, then a joining table between account id and role id.
Account table can handle all the details like first name, last name, address, etc.
 

Solarion

Honorary Master
Joined
Nov 14, 2012
Messages
20,666
Just coming back to this. This is the basic table structure. There are others but really this the heart of it all. It is open to suggestions/name changes/pie flinging. Come at me bros.

Jobs-Employees.png

I have the remuneration rate in two places, one in the Users table and another in UserJobs (worst fkn name ever) because I want the option to change their rate when I see fit, or feel like it basically.
 

rrh

Expert Member
Joined
Nov 29, 2005
Messages
3,452
Don't forget that attributes such as password and email address could very well be linked more to the user's role than the actual user i.e. be very careful when deciding where to store the data.

I would probably store any such attributes in their own entity tables, along with a link entity that includes a user ID and a role ID.
 

Solarion

Honorary Master
Joined
Nov 14, 2012
Messages
20,666
Still going with this. On the MVC side I've incorporated a nice bootstrap template, sb-admin, with a lot of the extra fluff chopped out. Its now a login, registration, email confirmation, forgot password, and an admin area for editing users, user's roles. All MVC core with authentication and authorization. Working on the jobs area now.

Edit: Thanks rrh I will just need to think about that or put on todo list. Smashing this project to get it finished and then will come back to that before I go forward.

Edit: FYI this is on the API side. I have limited what data is returned when polling user information to basically this:

Id
UserTypeId
FirstName
LastName
UserName
Email
EmailConfirmed
PhoneNumber
ProfilePicture
 
Last edited:

rrh

Expert Member
Joined
Nov 29, 2005
Messages
3,452
Could you expand on that a bit more?
A Person has a name, a date-of-birth and a national ID number.

As a private individual you might have an private computer (protected by its password) and a private email address,

As an employee you will have you will have an work computer (protected by its password) and a work email address.

You might also be able to access your work computer remotely via a company VPN, also with its own password.

At work you might function in multiples roles e.g. as a developer and a DBA. These would require different roles with different access rights, with the role selection being defined by the login credentials used.

Etc, etc.
 

Solarion

Honorary Master
Joined
Nov 14, 2012
Messages
20,666
A Person has a name, a date-of-birth and a national ID number.

As a private individual you might have an private computer (protected by its password) and a private email address,

As an employee you will have you will have an work computer (protected by its password) and a work email address.

You might also be able to access your work computer remotely via a company VPN, also with its own password.

At work you might function in multiples roles e.g. as a developer and a DBA. These would require different roles with different access rights, with the role selection being defined by the login credentials used.

Etc, etc.

I'll explain how my accounts work possibly later today. Essentially everyone can have multiple accounts if they wish and in my scenario they would need to contact the Admin or a Manager to assign them certain roles and permissions for each account. An account can also have multiple roles. The way I have it set up is something like DBA type of user is stored in a table called UserType and that is linked to a particular IdentityUser, with that account having it's own Role/roles.

I will go into more detail later and get some more feedback from you guys. I'm keen to just get it all 100% right.
 
Top