A design question around AspNetIdentity

[Solarion]

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]

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]

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.

 

[DA-LION-619]

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

Inheritance - EF Core

How to configure entity type inheritance using Entity Framework Core

docs.microsoft.com

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.

 

[Johnatan56]

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]

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.

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.

 

[_kabal_]

how about “TimeLog” instead of “UserJob”?

 

[Solarion]

how about “TimeLog” instead of “UserJob”?

That's great thanks kabal. That's essentially what it is yeah so makes sense.

 

[rrh]

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]

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

 

[DA-LION-619]

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

Could you expand on that a bit more?

 

[rrh]

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.