Structure/Nomenclature of Tables

Asked

Viewed 527 times

4

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 of how to build two of them, the "inherited" permissions of Systems and Groups, this means that when, for example, the user is a administrator, he has administrator permissions and, inherits, the permissions of user 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 table of Systems*:

CREATE TABLE [dbo].[Systems](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,

The table of Groups*:

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 Group Inherited 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 the CREATE TABLE for brevity in the code.
**: These tables are under a strange name, I would like some other suggestion to them.

UPDATING

Explanation of the structure, the table of Systems and Groups are obvious, however, the table InheritingSystem and InheritingGroup are referring to the permissions inherited from their respective tables, therefore the IdSystem and IdGroup refer to System and Group, respectively, which has the highest permission, and the IdInheritedSystem and IdInheritedGroup are the Systems and Groups, respectively, which will complement the permission.

Data from the System table:

Dados da tabela de Sistema

Data from the Group table:

Dados da tabela de Grupo

Data from the Inherited Systems table:

Dados da tabela de Sistema Herdados

Data from the Inherited Groups table:

Dados da tabela de Grupos Herdados

sql database sql-server modeling
  • 1

    I may be quite mistaken, but suggested names generate responses based on opinions

  • 1

    It’s not, but that’s just a small part of the question.

  • @Caffé, could you check if the images appear for you, please? Here they do not appear.

  • @Caffé, I explained better the functionality of this part.

  • I think I get it. And where does the user come into this story? Is his table relevant to this question? You can get an idea of what you want, but you can not know the solution you have so far. Maybe you do not escape from posting the 9 tables.

  • So @Caffé, actually, I have no interest in leaving these 9 tables, even because, as far as I researched, this structure is the only structure that will suit me in this question and the user is not relevant to this issue, because my doubt revolves around the construction of tables Herança and their relationships, you understand?

  • 2

    Okay. Dealing only with what we have there, what it seems to me is that you want to create a group and add to it permissions of other existing groups; and the same for systems. I believe that this language is simpler and clearer than "inheritance". In this case, the tables would be: GroupAddedGroups (Id, IdGroup, IdAddedGroup) and SystemAddedSystems (Id, IdSystem, IdAddedSystem). Of course Id in these tables is playing the role if surrogate key and can be dispensed with (depends on other design decisions). What do you think?

  • @Caffé, carry this your comment for an answer, that I will mark it as correct. Another thing, you have some hint about modeling, apart from these two tables, in the general context, you think it’s good for the role you will play?

  • You would have to see the other tables. There are many ways to set user permissions. But I don’t see anything wrong with what you’ve done so far (apart from the nomenclature you’re already improving). Maybe a good additional tip is: don’t plan a complex security system you’re not sure about right now that you’re going to need. On my way out, I’d make a system hard coded of groups, each user would belong to a group, and that’s it. If the customer wants more flexibility in the future, add more flexibility in the future.

  • @Caffé, I understand what you mean, but in this case, I already want to develop something complex, and "complete" now, not to have to rediscover in the future, since I already know I would have to perfect the code. But thanks for your help! :)

Show 5 more comments

3 answers

4


Dealing only with what we have there, what it seems to me is that you want to create a group and add to it permissions of other existing groups; and the same for systems.

I believe this language "add groups to groups" be simpler and clearer than "inheritance".

In this case, the tables would be:

  • Groupaddedgroups (Id, Idgroup, Idaddedgroup)
  • Systemaddedsystems (Id, Idsystem, Idaddedsystem)

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

3

SystemInheritedPermissions and GroupInheritedPermissions do not believe it is a strange name, or put in Portuguese.

  • So I liked the name, but I didn’t use it Permissions in no other table, nor the main ones, as quoted in the code, so I think would leave the standard and in Portuguese will leave the standard, because it is all in English.

  • 2

    I understand, so, as you have your standards, I believe this cool so, or changes from InheritingSystem for SystemInheritances, that instead of herdar or herdando, will stay heranças, in the case HerançasSistema.

  • I believe I will put your answer as the correct one, but I would still like an opinion on the modeling of permissions.

  • Can your code be viewed and maintained by developers from another country? why in English? rsrs[Curiosity]

  • @Rod, English is only by convention itself and because as use . NET, everything is English, I end up keeping all the code in English to be consonant with the syntax. And no, the code is mine and will not be maintained by foreigners, at least it is not in my plans that, rs.

  • The vast majority of languages are in English, or all, rs, use. net also and I see no problem in using Portuguese, as long as it is clear what is the table/ method or what to name, but anyway...like is taste, rsrs and we never know if it will not be maintained by foreigners kkk

  • @Rod, I expressed myself wrong, rs... I just wanted to exemplify why to be in English. So, yeah, the gringa is dominating the "rest of the world", rs.

Show 2 more comments

0

Browser other questions tagged sql database sql-server modeling

You are not signed in. Login or sign up in order to post.