关于分层MySQL数据库设计的建议

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

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
);