title: Better way to reference foreign key in database
body: <p>can you suggest the best practices for database optimization?
  Here is my database schema,</p>
  
  <pre><code>group(id, name, status)
  users(id, group_id, parent_id, username, password)
  
  company(id, name, address, phone)
  agent(id, agent_code, name, address, phone)
  </code></pre>
  
  <p>In this schema i have four kinds of user group as super admin, company user, agent user and end user.</p>
  
  <p>What is the best way to refer the users of each entities(company, agent)?</p>
  
  <p>I have two options</p>
  
  <p>A. Foreign key in users table as company_id and agent_id</p>
  
  <pre><code>users(id, group_id, parent_id, company_id, agent_id, username, password)
  </code></pre>
  
  <p>B. Foreign key as user_id in the company and agent table</p>
  
  <pre><code>company(id, user_id, name, address, phone)
  agent(id, user_id, agent_code, name, address, phone)
  </code></pre>
  
  <p>I am using mysql as database and using cascade reference. 
  When I delete a company/agent, associated users are automatically deleted as per option A. But in case of option B I must run second query to delete the associated users of  a company/agent. So what do you suggest???</p>
  
  <p>UPDATE:
  Forgot to mentioned about child users. A user of a company/agent can have sub users, referred by parent_id in the users table.</p>