title: Proper Table Design - How to create child table
body: <p>I am not sure if this is the right forum - it is more about designing sql tables
  I was trying to find answers online - no luck</p>
  
  <p>I have menu permission table that list </p>
  
  <ul>
  <li><strong>permissionID</strong> - sequential number</li>
  <li><strong>userID</strong></li>
  <li><strong>menuID</strong> - id of the menu that user has access to</li>
  </ul>
  
  <p>Once user gets to the menu - it may have some further restrictions, for instance there could be drop down with city names and user will have access only to some of them (or all).</p>
  
  <p>To accomplish that I created another table called let's say menu permission child.
  It has...</p>
  
  <ul>
  <li><strong>permissionID</strong></li>
  <li><strong>permissionChildID</strong></li>
  <li><strong>permissionOption</strong></li>
  </ul>
  
  <p>Now...</p>
  
  <ul>
  <li>if user has permission to view all cities - I set permissionOption to -1</li>
  <li>if user has permission to some of the cities - I create separate entry in child table for each city</li>
  </ul>
  
  <p>Query...</p>
  
  <pre><code>SELECT CityID, CityName
  FROM Location
  WHERE (CityID IN (
    SELECT permission_option 
    FROM PermissionMenuChild 
    WHERE userID = 1)) 
  </code></pre>
  
  <p>It works - but can someone come with more elegant way to do it?</p>