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

关于分层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!

图片转代码服务由CSDN问答提供 功能建议

我是数据库设计的新手,只是想了解一下我是否会这样做 以合乎逻辑的方式。 我正在构建一个简单的MySQL数据库,用户可以通过该数据库将项目上传到以前存在的(不变的)分层树。

一个简单的例子:

第1节

分区1.1
细分1.1.1
细分1.1.2 < p> Division 1.2
细分1.2.1
细分1.2.2

第2节

分区2.1
细分2.1.1
细分2.1.2

分区2.2
细分2.2.1
细分2.2.2

树的结构不会改变,用户只需上传属于细分的产品(行业特定的组织大量产品的方式) 。 我已经对邻接列表和嵌套集进行了研究,但我倾向于3个独立的表,每个表引用其父父键(看到树的顶层几乎不会改变)。 当上传新产品时,它将引用其所有三个父母(如果它是根据细分1.1.2提交的,则它必然是第1部分第1部分的一部分)。 最终的树将有4个部分,每个部分有10个分区,每个分区有10个分区。 这是否有意义作为一种启动策略?

与数据库的交互或多或少局限于输入信息并对其进行准确分类,然后能够显示已在部门,部门或细分中提交了多少产品 。 该库将显示在一系列下拉列表中,单击列表项将显示存储的信息。

任何建议或对文献/教程的参考将不胜感激!< / p>

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

4条回答 默认 最新

  • 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 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 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 2013-09-24 22:57
    打赏 评论

相关推荐 更多相似问题