I am developing a permission system and I have a universe of 9 tables, which form the final permission for the user to use my systems, however, I am in doubt about how to build two of them, that of "inherited" permissions of Systems and Groups, this means that when, for example, the user is an administrator , he has administrator permissions and inherits / em> and visitor .
What I would like is to put together a structure that is easy to identify this and, most importantly, to manipulate, so follow the tables:
The Systems table *:
CREATE TABLE [dbo].[Systems](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
The Groups table *:
CREATE TABLE [dbo].[Groups](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
The Inherited System Permissions table **:
CREATE TABLE [dbo].[InheritingSystem](
[Id] [int] IDENTITY(1,1) NOT NULL,
[IdSystem] [int] NOT NULL,
[IdInheritedSystem] [int] NOT NULL,
The Inherited Group Permissions table **:
CREATE TABLE [dbo].[InheritingGroup](
[Id] [int] IDENTITY(1,1) NOT NULL,
[IdGroup] [int] NOT NULL,
[IdInheritedGroup] [int] NOT NULL,
*: I cut the rest of CREATE TABLE
to give brevity in the code.
**: These tables have a strange name, I would like some other suggestion for them.
UPDATE
Explanation about the structure, the Systems and Groups tables are obvious, however, the InheritingSystem
and InheritingGroup
tables refer to the inherited permissions of their respective tables, therefore, IdSystem
and IdGroup
refer to the System and Group , respectively, which has the highest permission, and IdInheritedSystem
and IdInheritedGroup
are the Systems and Groups , respectively, which will complement the permission.
System table data:
Grouptabledata:
Inherited Systems table data:
InheritedGroupstabledata: