title: mySQL store file details in one or many database table design and best way of creating parent and child categories
body: <p>----- PHP and mySQL -----</p>
  
  <p>I have two quick questions need some advice. </p>
  
  <p>On my site I will allow users to upload following files -  PDF/Videos/Photos. All files uploaded by the user are shown on a profile page. All uploaded files can be searched by name or tags and file type.</p>
  
  <p>What would be the best mysql database design? </p>
  
  <ol>
  <li><p>Store all files in one table, easier to display on user’s profile page and searching by type and etc.</p></li>
  <li><p>One table per type e.g. pdf, videos and photos &lt;- this might be better for performance but for searching I don’t know?</p></li>
  </ol>
  
  <p>Second question is, I allow users to create their own menus/categories with parent and children  categories for example:</p>
  
  <ul>
  <li>->parent category</li>
  <li><ul>
  <li><ul>
  <li><ul>
  <li>> child category</li>
  </ul></li>
  </ul></li>
  </ul></li>
  <li><ul>
  <li><ul>
  <li><ul>
  <li>> child category</li>
  </ul></li>
  </ul></li>
  </ul></li>
  <li><p>->parent category</p></li>
  <li><ul>
  <li><ul>
  <li><ul>
  <li>> child category</li>
  </ul></li>
  </ul></li>
  </ul></li>
  <li><ul>
  <li><ul>
  <li><ul>
  <li>> child category</li>
  </ul></li>
  </ul></li>
  </ul></li>
  </ul>
  
  <p>At moment I have two database tables, one stores all the parent categories for each user and second store child category with foreign key (id) to parent category. </p>
  
  <p>To get all the categories I first get all the parent categories and using a foreach loop.
  I call a function  within the loop to get the children categories by parent id. </p>
  
  <p>I want to know is this the best approach of doing this or can this be done in mySQL query without looping?</p>
  
  <p>thanks guys !!!</p>