title: Permissions: bitwise operations or many-to-many child table?
body: <p>I am trying to understand the best way to work with permissions and as far as I'm aware there are two major options.</p>
  
  <p>The first option is to use bitwise operations, and for this I would use the following database structure:</p>
  
  <p><strong>users</strong></p>
  
  <pre><code>user_id | user_permission
  ---------------------
  1       | 15
  2       | 1
  </code></pre>
  
  <p><strong>permissions</strong></p>
  
  <pre><code>permission_id | permission_name
  -----------------------
  1             | Read
  2             | Write
  4             | Execute
  8             | Delete
  </code></pre>
  
  <p>And then to check that the user has permission I would use the operation:</p>
  
  <pre><code>$user_permission &amp; $permission_id
  </code></pre>
  
  <p>The main benefits I see to this are:</p>
  
  <ol>
  <li>Trivial to set, get, and validate permissions</li>
  <li>Less storage (no child database; no additional rows per user permission)</li>
  </ol>
  
  <p>The main drawbacks I see to this are:</p>
  
  <ol>
  <li>Listing users' permissions slightly more complicated</li>
  <li>Cannot use foreign key constraints</li>
  <li>Limited permissions (64 if using <code>BIGINT</code>)</li>
  </ol>
  
  <p>The second option is to use a many-to-many child table, and for this I would use the following database structure:</p>
  
  <p><strong>users</strong></p>
  
  <pre><code>user_id
  -------
  1      
  2      
  </code></pre>
  
  <p><strong>permissions</strong></p>
  
  <pre><code>permission_id | permission_name
  -----------------------
  1             | Read
  2             | Write
  3             | Execute
  4             | Delete
  </code></pre>
  
  <p><strong>user_permissions</strong></p>
  
  <pre><code>user_id | permission_id
  -----------------------
  1       | 1
  1       | 2
  1       | 3
  1       | 4
  2       | 1
  </code></pre>
  
  <p>And then to check that the user has permission I would use the operation (where <code>$user_permission</code> is an array of <code>permission_id</code>s):</p>
  
  <pre><code>in_array($permission_id, $user_permission);
  </code></pre>
  
  <p>The main benefits I see to this are:</p>
  
  <ol>
  <li>Can use foreign key constraints</li>
  <li>Trivial to list users' permissions</li>
  <li>Allows for a far greater number of permissions</li>
  </ol>
  
  <p>The main drawbacks I see to this are:</p>
  
  <ol>
  <li>Greater storage (child database; additional rows per user permission)</li>
  <li>Setting and getting permissions slightly more complicated</li>
  </ol>
  
  <p><strong>Question</strong></p>
  
  <p>Which of these would be the better option? I see benefits and drawbacks to each and am unsure which would be more suitable. Although I am aware that context probably plays a role; so in which situations would bitwise operations be better and in which would a many-to-many child table be better? Or is there a third option of which I'm unaware?</p>
  
  <p>I'm currently more inclined to use a many-to-many table for the benefits of foreign key constraints and a greater number of permission possibilities, but I wonder if I'm missing something else; bitwise operation permissions seem to be quite prevalent so I'd assume there is a good reason for using them.</p>