-1

I am looking for a schema to give to "fruits" permission by:

  1. User
  2. Group
  3. User and Group simultaneously. I mean "OR" "AND"

The idea is to get the best performace to do that relationship.

current schema here

DarkRob
  • 3,843
  • 1
  • 10
  • 27
johnnytolengo
  • 9
  • 1
  • 3
  • how do you relate fruits with user...? – DarkRob Oct 05 '19 at 10:34
  • 1
    If it was me, I'd be tempted to force all users to belong to groups, even if it was a group of 1 – Strawberry Oct 05 '19 at 10:59
  • Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Oct 07 '19 at 00:25
  • 1
    There's no such thing as "better"/"best" in engineering unless *you* define it. Also unfortunately all reasonable practical definitions require a ridiculous amount of experience with a ridiculous number of factors that interact with chaotic sensitivity to details. Make straightforward designs. When you demonstrate via measurement that a design and all alternatives you can think of have problems (whatever that means at the time), then ask a very specific question. Which should also define "better"/"best". [Strategy for “Which is better” questions](https://meta.stackexchange.com/q/204461) – philipxy Oct 09 '19 at 12:26
  • What kind of permissions does a fruit have? Permission to rot? And then stink? – Rick James Oct 14 '19 at 23:31

1 Answers1

0

The idea is to get the best performace to do that relationship.

Is that the idea? Or is the idea to do it correctly? There are infinitely many fast solutions to incorrect results.

The problem you pose, by the way, is pervasive in security: how to characterize groups for authorization. The groups are hard to define and harder to administer.

If the id for users and groups are taken from the same domain -- that is, no user id is also a a group id -- then you can use the union of user and groups. If groups can also be group members (not you've as shown), you have a recursive definition, which SQL supports. You can then query a recursive view for permission membership. Now all you need is a simple table to reflect them: 

create table permissions (
    int member_id not null -- user or group, 
    char permission not null 
        check permission in ('R', 'W', 'X') -- or whatever
);

If users and groups are in distinct domains, the query is the same, but the view and the permission table both need an additional column to reflect whether it's a user or group.

James K. Lowden
  • 7,574
  • 1
  • 16
  • 31