du6333137
du6333137
2012-03-22 19:51
浏览 30
已采纳

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

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!

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

4条回答 默认 最新

  • doutan5798
    doutan5798 2012-03-22 20:12
    已采纳

    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
    );
    
    点赞 评论
  • duandu6497
    duandu6497 2012-03-22 20:04

    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.

    点赞 评论
  • dongyi7669
    dongyi7669 2012-03-22 20:04

    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

    点赞 评论
  • doudaifu6083
    doudaifu6083 2013-09-24 22:57
    点赞 评论

相关推荐