We have roles like Owner,Editor,Viewer etc... And every roles have different privileges like download,share,edit,view ect...

I have created two database design for this functionality. Which database design I have to implement ?

1) Table   -> Roles 
   Columns -> Id
              Name
              isDownload
              isShare开发者_Python百科
              isView

2) Table   -> Roles
   Column  -> Id
              Name
              Description

   Table   -> Privileges
   Column  -> Id 
              Name
              Description

   Table   -> RolesPrivileges
   Column  -> Id
              RoleId
              PrivilegeId

What is the pron and cons with these designs ? Which one I have to implement ? Which is more scalable and maintainable ? Why ?

---

The second one is the way to go. If you need to create a new role, you don't have to modify the table structures, just create a new role, and associate the privlidges etc. Biggest problem with the first design is that any changes in roles/privlidges required table strucuture changes.

---

Things like this depend a lot upon how your database is going to be used - factors like:

1. Flexibility - is it possible you may need to add additional roles/privileges in the future? If so, go for the more expansive and flexible table structure in #2.
2. Complexity - if your database is any larger than a casual tiny system, I'd recommend going for #2. However, if it is very small and informally used, it may be worth your saved time to go with the simpler system.
3. Performance - #2 is obviously a touch more complicated and is likely to require more queries, especially if you have any cases where you might have to do many at once. However, this can be well mitigated through proper use of database indexing.

---

Sure, the first one is the way to go for it:

Table   -> Roles 
Columns -> Id
           Name
           isDownload
           isShare
           isView

• is a simple design
• you can read roles and privileges from the same table without needing JOINs
• has lesser space requirements than the second alternative

However if you have dynamic privileges with privileges added or removed frequently, you might think of approach 2. But otherwise keeping a single table is fine.

---

1) Table   -> Roles 
   Columns -> Id
              Name
              Description
              isDownload
              isShare
              isView


2) Table   -> UserRolesPrivileges
   Column  -> Id
              RoleId
              UserId

UserId comes from the User registration table.

Each user is associated with the respective Roles In roles table I suppose you will have different roles and each role will be defined what action it can perform.

I dont see any use of Privileges tables, as long as the user is associated with the roles

---

I would like to make a suggestion about option 1 that may elude some of its criticism. Instead of using boolean values for every possible permission, use a bit mask instead.

First an example

(responsible_mask INT)

def Roles

    RESPONSIBILITES = [ :switchboard, :content_manager, :network_administrator, :financial_manager, :receives_contact_emails ]

    def responsibilites=(responsibilites)
        self.responsible_mask = ([*responsibilites].map(&:to_sym) & RESPONSIBILITES).map { |r| 2**RESPONSIBILITES.index(r) }.sum
    end

    def responsibilites
        RESPONSIBILITES.reject { |r| ((responsible_mask || 0) & 2**RESPONSIBILITES.index(r)).zero? }
    end

    def responsibilites_symbols
        responsibilites.map(&:to_sym)
    end

    def responsible?(responsibility="none")
        responsibilities_symbols.includes?(responsibility.to_sym)
    end
end

It is easy to add more responsibilities at any time.

And now why?

In my opinion, this is better practice. I can't see a reason why I would create a form to add another responsibility (or privilege in your case) without also placing hooks in my code to use that responsibility. I only need this information to determine if I should allow functionality; it has no other purpose. Of course I still want an admin to be able to create roles and assign responsibilities to that role but it will always be that fixed set.

It also makes SQL queries more complex–adding another join. Slower. Harder to debug.

It is a pain to remember to build this table of static data when deloying to another server.