Table Structure / Nomenclature

3

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:

    
asked by anonymous 19.11.2014 / 14:33

3 answers

3

By just looking at what we have there, what I think is that you want to create a group and add permissions to it from other existing groups; and the same for systems.

I believe this language "adding groups to groups" is simpler and clearer than inheritance >.

In this case, the tables would be:

  • GroupAddedGroups (Id, IdGroup, IdAddedGroup)
  • SystemAddedSystems (Id, IdSystem, IdAddedSystem)

Of course, Id in these tables is playing the role if surrogate key and can be waived (depends on other design decisions).

    
19.11.2014 / 21:16
3

SystemInheritedPermissions and GroupInheritedPermissions I can not believe it's a strange name, or put it in Portuguese anyway.

    
19.11.2014 / 18:21
0

Dude, I gave you my share on the subject in the answer below, see if you can attend.

What naming conventions in database creation, tables and columns in mysql

    
02.03.2018 / 05:12