mysql DB design and query optimazation

Question

I am working on a project where I need to design a table like directory management. I am just a beginner in DB, so I really need your guys' expertise. my current thought for database design can 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 see the parent_id is pointing its own table PK id. the constrain complys with the real world like folders can contain folders, files can not has children,etc...

most used query scenario would be:

given an id, finds its all subfiles(include folder and file), for each file, indicates whether it has children or not.
given an id, finds its all ancestors id(parents, grandparent...)

considering a large scale application, questions:

do you think the schema design reasonable? if not,please suggest one.
for those two scenarios, how can I write robot queries that won't suffer the performance.

thanks for any help.

if parent_id is FK to the same table 0 is not allowed and has to be NULL — Davide Piras
– Davide Piras, Commented Jul 8, 2011 at 12:18
Identify the queries you want to write and create indices on same column (in same order) as you use in the where clause. — Davide Piras
– Davide Piras, Commented Jul 8, 2011 at 12:19
no, folder1 cannot be contained in itself!! has to be NULL or you create a record called ROOT with ID = 0 — Davide Piras
– Davide Piras, Commented Jul 8, 2011 at 12:20

j0k · Accepted Answer · 2012-10-06 09:48:08Z

1

You can consider this way:

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      -----       2-1
  4   folder4  ---  folder    ---  2011-2-3       -----      3-2-1
  5   file1    -----  file      -----  2011-2-3    -----         4-3-2-1

Put the hierarchy info in parent_id which states all its ancestors.

When you wanna add a new folder under folder4 for e.g. You can simply attach 4- to the parent_id value of folder4 and make it your new folder's parent_id.

In this way you dont have to recursively find out all the ancestors.

edited Oct 6, 2012 at 9:48

j0k

22.8k28 gold badges81 silver badges90 bronze badges

answered Oct 6, 2012 at 8:14

linehrr

1,76819 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SergeS · Accepted Answer · 2011-07-08 13:36:47Z

0

Architecture with parent_id isn't good for listing all parent nodes and all child nodes - you will need a recursive procedure to do this.

take a look to this article http://www.sitepoint.com/hierarchical-data-database-2/ , only problem is adding records - but can be simplified via triggers

For correct indexes see comment from Davide Piras

answered Jul 8, 2011 at 13:36

SergeS

11.9k3 gold badges31 silver badges35 bronze badges

2 Comments

bingjie2680 Over a year ago

the article refered is really useful, I am thinking taking this approach. 'but can be simplified via triggers' ?? could you please elaborate a bit about this. thank you for your answer.

SergeS Over a year ago

triggers ( MySQL 5.0+ ) - it is procedure which is called when some kond of operation is executed on table - in your case this will be a trigger on insert or on update, trigger will recalculate all left and right indexes - try to find out

Collectives™ on Stack Overflow

mysql DB design and query optimazation

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related