title: what is a better way than arrays overlap? (postgresql)
body: <p>I am building an app for a business company, and they need to control who sees which reports by project and roles, the report can belong to one project and can be seen by many roles (employees roles).<br>
  so when the report is submitted it is tagged with the project and roles, like "project1" and {"manager","seller"} , so for example the employees who are working on project1 and are managers can see this report.
  The way i do it now is very much depends on arrays, this is what i have:</p>
  
  <p><strong>reports table:</strong><br>
  project (string)<br>
  roles (array of strings)  </p>
  
  <p><strong>employees table:</strong><br>
  projects (array of strings)  // all the projects the employee working/worked on<br>
  roles (array of strings)  // employees can have many roles </p>
  
  <p>when querying the reports the employee can see, i do something like this:  </p>
  
  <pre><code>select * 
  from reports 
  WHERE (employee.roles &amp;&amp; report.roles) AND (report.project = ANY (employee.projects))
  </code></pre>
  
  <p>i use postgresql</p>
  
  <p>the problem is i think this will not have a good performance (i'm not sure)<br>
  the only way i know to speed this query is making a GIN index on reports (roles) column, to make the overlap faster  </p>
  
  <p>beside performance this tip <a href="http://www.postgresql.org/docs/9.1/static/arrays.html" rel="nofollow">here</a>, just made me worry:</p>
  
  <blockquote>
    <p>Tip: Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.</p>
  </blockquote>
  
  <p>so is there a much better design to do this, or this will just works fine?</p>