How to correctly resolve circular relationships between tables in a database? And is it possible to implement such inheritance in the Entity Framework?

Question

SQL

There are tables: Users, Groups, Roles. In the Users table there is a link to the so-called Groups (it is filled in if the user belongs to some group, and remains NULL if it does not belong). In the Groups table there is a reference to the so-called Roles, which is always non-nullable, because each group corresponds to 1 role. And in the Users table, there is also a link to the so-called Roles, because Each user must have 1 role. And there is a requirement that when changing the role for a group, the role of all users belonging to this group will automatically change.
Problem: we got circular relationships between three tables, i.e. t. Users refer to t. Roles both directly (let's call this connection a direct link), and through t. Groups as an intermediary, tk. the Groups table refers to t. Roles (let's call this connection an indirect link).
Question1: is it normal to design a database schema in this way, or is it bad when there is a circular relationship of tables (that is, when we can choose from various combinations of table joins to get some data)?
Further, there are options (we assume that our direct link is a regular foreign key column in Users, leading to Roles): 1) make the field-direct link to the Role in the Users table nullable and fill it only for users , which do not belong to any group, and for the rest of the users, take the role using the indirect link GroupID->RoleID. 2) make the field-direct link to the Role in the Users table non-nullable and fill it in for all users (here, for data consistency, it is necessary that for group users the direct link i.e. Role and the indirect link GroupID->RoleID lead to the same role ).
In the case of the first option, the downside is that in order to get the role, we need to join not 2 tables, but 3 (that is, it should be slower). In the case of the second option, the downside is that group users will actually have 2 role links, although our user has only 1 role (even if we assume that both links will always lead to the same role, I don’t like the fact the fact that there is no unambiguity in the database about which link we should get the user's role from), besides, when changing the role for a group, we must additionally not forget to change the direct link to the role for users from this group. The disadvantages of both options is that we need an additional mechanism to control the consistency of data on both links, i.e. that with option 1, we will never insert a group user into the database with a non-nullable direct link to the group,
Question 2 : which option is better, 1 or 2? And does it depend on the number of joins that should be made via an indirect link, i.e. for example, if we need to join more than 10 tables, then to improve performance, we better add a direct link, if there are less than 10 tables, then to reduce ambiguity in the database, we better leave a direct link for non-group users equal to NULL?
Further, there are 2 options for storing a direct link from the user to the role: 1) a regular field in the table that stores real data (foreign key referring to the role) 2) a "virtual" field in the table (does not store data, but returns data from another column ). In the case of the first option, we must have a mechanism by which we must control the consistency of data if we have more than one link to an entity. This can be done in application code, but I don't like this idea because I believe that the database should control the consistency of its data itself. As I understand it, the best option for this would be to write an INSTEAD OF trigger to change Users (for the inserted user, we ourselves get a link to the role via the indirect link GroupID-> RoleID and insert into the database) and an AFTER trigger to change Groups (after changing the link to the role of the group, we look for all users in this group and change the links to the role for them). The downside of this option is an extra field in the database that stores a duplicate of data that can already be obtained through an indirect link, which needs to be additionally validated during changes and which will not be indicated in any way that this should be an immutable field (i.e. the bad thing is that writers update\insert scripts or Entity Framework may attempt to update this field even though the trigger will prevent them from doing so). In the case of the second option - as I understand it, a direct link can be made as a calculated field, on which to hang a function that will return the role by the indirect link GroupID->RoleID. The disadvantages of this - it will not be possible to make this direct link a foreign key (because calculated columns cannot be foreign keys), the number of table joins is increased to find a link to the role, and most importantly, this method is stupidly not suitable for users who do not belong to the group (because such users do not have an indirect link GroupID- >RoleID), i.e. we cannot generally use this option with such a database schema.
Question 3 : what is the best way to implement this (and are there other ways)?
In general, I would rather change the database schema in this situation as follows: I added the GroupUser table and the IndividualUser table, which would refer to the so-called Users with a 1-to-1 relationship. Then the link to the t. Groups could be moved from the t. Users to the t. GroupUsers, and the link to the t. Roles could be moved from the t. Users to the t. IndividualUsers. Then the possibility of violating the integrity of links disappears, tk. all group users will store a role reference via an indirect reference, and all non-group users will store a role reference via a direct reference. But as far as I know there is no support for creating entity inheritance in Entity Framework. That is, it turns out that in this version, in the dbContext model, I have to create classes with inheritance:
User{}
GroupUser: User{}
IndividualUser: User{}
And if I want to get all users, I write something like:
List users = dbContext.Users.ToList();
then the Entity must read the data not only from the Users table, but also from the GroupUsers table and the IndividualUsers table and, accordingly, create objects of the GroupUser and IndividualUser classes. As far as I know, this is not possible in EF?
Question 4 : Is it possible to implement such a scheme with inheritance in the Entity Framework?

Answer 1

What is the best way to implement this (and are there other ways)?

Yes. The tables User - Group - Role are linked by a 1-M relationship (foreign key). I give a hint: the user is assigned a group by default. It can have a link to a role or not - you decide your own contradiction: there is no group, but there must be a role. If you decide that the role is required, then name the group the same as the role.
It's also nice to get acquainted with any ready-made system User, Group, Role. There are multiple user associations with groups and roles. The resulting roles are the sum of group roles and individual roles. Complication: the role can be both permissive and prohibitive. MS in this case grants the ban privilege.