Advice on Hierarchical MySQL Database Design

Question

I'm new to database design and just wanted a few opinions as to whether or not I'm going about this in a logical way. I'm building a simple MySQL database through which users will upload items to a previously existing (unchanging) hierarchical tree.

For a simple example:

Section 1

   Division 1.1
      Subdivision 1.1.1
      Subdivision 1.1.2

   Division 1.2
      Subdivision 1.2.1
      Subdivision 1.2.2

Section 2

   Division 2.1
      Subdivision 2.1.1
      Subdivision 2.1.2

   Division 2.2
      Subdivision 2.2.1
      Subdivision 2.2.2

The structure of the tree will not change, the users will simply upload products which will fall under the subdivisions (an industry-specific way to organize a large number of products). I've done research on adjacency lists and nested sets, but am leaning towards 3 separate tables each referencing its parents primary key (seeing as the top levels of the tree will virtually never change). When a new product is uploaded, it will reference all three of its parents (if it is filed under subdivision 1.1.2, it is necessarily part of section 1, division 1). The final tree will have 4 sections, with 10 divisions in each section and 10 subdivisions in each division. Does this make sense as a starting strategy?

Interaction with the database is more-or-less limited to inputting information and categorizing it accurately, and then being able to show how many products one has filed in either section, division, or subdivision. The library will be displayed in a series of drop-down lists, and clicking a list item will bring up the stored info.

Any recommendations or references to literature/tutorials would be appreciated!

Answer 1

Have a look at nested sets and nested intervals:

• http://en.wikipedia.org/wiki/Nested_set_model

• http://en.wikipedia.org/wiki/Nested_intervals

Answer 2

A good solution would to have a recursive table.

Check this post on StackOverflow: Hierarchical Data in MySQL

This way, your design will support having more levels down the tree.

Other interesting articles about this topic:

Managing Hierarchical Data in MySQL

Hierarchical data in MySQL: parents and children in one query

Hierarchical data in MySQL: easy and fast

Recursion-less storage of hierarchical data in a relational database

Answer 3

Because "the structure of the tree will not change" you need section, division and subdivision tables (also products).

create table section (
  id int primary key,
  name varchar(100) 
);

create table division (
  id int primary key,
  name varchar(100) ,
  section_id int references section
);

create table subdivision (
  id int primary key,
  name varchar(100) ,
  division_id int references division
);

create table product (
  id int primary key,
  name varchar(100) ,
  subdivision_id int references subdivision
);

For others requerimets, for example:

• Unknow depth tree.
• Assign products to several tree levels.

You will look for a parent-child soluction, for example:

create table tree (
  id int primary key,
  parent_id int null references tree,
  name varchar(100) 
);

create table product (
  id int primary key,
  name varchar(100) ,
  subdivision_id int references tree
);

Answer 4

Since the categories (sections) are more or less static, what you can do is assign each category a 'high' and a 'low' number, such as

catid  name        low      high
1      Section1     1       20
2      Div1.1       2       10
3      Div1.2       11      19
4      Section2     21      40
5      Div2.1       22      29
6      Div2.2       30      39

Then have a separate table for the content:

id   catid   content
1    2       fileA 
2    2       fileB
3    5       fileC

To then query ALL items under section 1, you simply query for all items in a category that have a high and low between 1 and 20. To get all items in Div2.1 (and under), you query for all items that have a category who's high and low are between 22 and 29. This makes it very easy to track the number of items under sub categories.

I forget the name of this actual approach (if there is a definitive one), but I've used it a number of times. For structures that dont change much, its a lot easier to work with than the traditional parent_id-child_id type structure.