I'm focusing on a task where I have to design a table like directory management. I'm only a beginner in DB, and so i actually need your guys' expertise. my current thought for database design could be illustred as below:

 id   name       type        create_time       parent_id
  1   folder1    folder      2011-2-3             
  2   folder2    folder      2011-2-3             1
  3   folder3    folder      2011-2-3             1
  4   folder4    folder      2011-2-3             1
  5   file1      file        2011-2-3             4

as you can tell parents_id is pointing its very own table PK id. the constrain complys using the real life like folders can contain folders, files can't has children,etc...

most used query scenario could be:

  1. given an id, finds its all subfiles(include folder and file), for every file, signifies whether or not this has children or otherwise.

  2. given an id, finds its all forefathers id(parents, grandparent...)

thinking about a massive application, questions:

  1. do you consider the schema design reasonable? otherwise,make sure you suggest one.
  2. for individuals two situations, how do i write robot queries that will not suffer the performance.

thank you for any help.

Architecture with parent_id is not great for listing all parent nodes and all sorts of child nodes - you may need a recursive procedure to get this done.

have a look for this article http://www.sitepoint.com/hierarchical-data-database-2/ , only issue is adding records - but could be simplified via triggers

For correct indexes see comment from Davide Piras