title: Nested foreign keys (with multiple tables)
body: <p>I have three tables:</p>
  
  <p>user: id</p>
  
  <p>folders: id, user_id</p>
  
  <p>words: id, folders_id</p>
  
  <p>(there are more columns in every tables, but they are for this problem irrelevant)</p>
  
  <p>A user contains several folders and each folder contains several words.</p>
  
  <p>So far so good. I interconnected these tables with foreign Keys with the help of MySQL Workbench.</p>
  
  <p>What MySQL-Workbench does for the first connection (folders.user_id -> user.id) is as I expected it. But when I add the second relationship (words.folders_id -> folders.id), It automatically generates an index over two columns: the expected one folders.id but also over the column of the first foreign key folders.user_id</p>
  
  <p><img src="https://www.google.com/search?q=foreign%20keys%20workbench&amp;oe=utf-8&amp;aq=t&amp;rls=org.mozilla%3ade%3aofficial&amp;client=firefox-a&amp;um=1&amp;ie=UTF-8&amp;hl=de&amp;tbm=isch&amp;source=og&amp;sa=N&amp;tab=wi&amp;ei=ECDiUKy-L47s9ASz5YDQCg&amp;biw=1536&amp;bih=660&amp;sei=EyDiUKTAFIb28wSK7IHIBg" alt="This is the screenshot of MySQL Workbench after having added the second foreign key. Automatically, this one is generated also over the column of the first foreign key: folders.user_id"></p>
  
  <p>I always thought, that duplicate data in a MySQL-Database is not a good solution. But why does MySQL-Workbench propose me to do it this way? The only advantage I can think of is, that I can select the user's id from a word directly without a JOIN. Is this the purpose of this index over two columns?</p>
  
  <p>Thanks for explaining me this phenomenon.</p>
  
  <hr>
  
  <p>I haven't found a solution yet, but when I search in google images for <a href="https://www.google.com/search?q=foreign%20keys%20workbench&amp;oe=utf-8&amp;aq=t&amp;rls=org.mozilla%3ade%3aofficial&amp;client=firefox-a&amp;um=1&amp;ie=UTF-8&amp;hl=de&amp;tbm=isch&amp;source=og&amp;sa=N&amp;tab=wi&amp;ei=ECDiUKy-L47s9ASz5YDQCg&amp;biw=1536&amp;bih=660&amp;sei=EyDiUKTAFIb28wSK7IHIBg" rel="nofollow">diagrams of relationships in MySQL Workbench</a>, these nested foreign keys never appear. So I assume they are not necessary and I just keep deleting them when MySQL Workbench creates them.</p>