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:
given an id, finds its all subfiles(include folder and file), for every file, signifies whether or not this has children or otherwise.
given an id, finds its all forefathers id(parents, grandparent...)
thinking about a massive application, questions:
- do you consider the schema design reasonable? otherwise,make sure you suggest one.
- 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