Mysql – Hierarchical Structure

MySQL

Every single user has say, 3 of GROUP_A, 10 GROUP_B's per GROUP_A, and 20 GROUP_C's per GROUP_B. And each of the 20 GROUP_C's involve lots of inserts/deletes…

I'm not an expert, but I've done research but it's all theoretical at this point of course, and I don't have hands on experience with the implementation that's for sure. I think my options are something like 'adjacency lists' or 'nested sets'?

Any guidance into the right direction would be very much appreciated!

Best Answer

One way to define your tables:

Users: user_id (primary key), name, etc

Group_A: user_id, group_a_id, group_a_name

Group_B: group_a_id, group_b_id, group_b_name

Group_C: group_b_id, group_c_id, group_c_name

Items: group_c_id, item_id, item_name, details

Related Solutions

MySQL function to return breadcrumb-like string from hierarchical structure

You can use the following function for returning Hierarchical structure for N no:of leaf nodes like "World > Europe > France > S1 > S2"

DELIMITER $$
 
DROP FUNCTION IF EXISTS `test`.`test` $$ 
CREATE FUNCTION `test`.`test`(V_Argument_Parent_ID integer) 
RETURNS varchar(1024) No
SQL BEGIN

  Declare Done Integer default 0;   
  Declare V_id Integer default 0;  
  Declare V_parent_id Integer default 0;

  Declare V_name varchar(1024) default '';   
  Declare V_Result varchar(1024) default '';   
  Declare V_TempResult varchar(1024) default '';

  Declare continue handler for SQLWARNING set Done = 1;   
  Declare continue handler for NOT FOUND set Done = 1;   
  DECLARE EXIT HANDLER FOR 1001 set Done = 1;

  SELECT id, name, parent_id into V_id, V_name, V_parent_id 
  FROM a
  where ID = V_Argument_Parent_ID;

  IF V_parent_id = V_id then
    Set V_Result = V_name;   Else
    Set V_TempResult = ''; 
    Set V_TempResult = concat_ws('',V_TempResult,'->',V_name);
 
    MainLoop: Loop

     set V_Argument_Parent_ID = V_parent_id;
     SELECT id, name, parent_id into V_id, V_name, V_parent_id 
     FROM a where ID = V_Argument_Parent_ID;

     Set V_TempResult = concat_ws('',V_TempResult,'->',V_name);
     IF V_parent_id = V_id then
         Leave MainLoop;
     End IF;

    End Loop MainLoop;

    Set V_Result = V_TempResult;

    set V_Result = trim(both '->' from V_Result);
 
  End IF;
 
  Return V_Result;
 
 
END $$
 
DELIMITER ;

Try out

Mysql – Hierarchical queries without CTEs

There are several things you can do

THING #1

Expand all VARCHAR(1024) to VARCHAR(10240) in all Stored Procedures. This will accommodate longer Family Tree Listings. This is vital because queue_children can grow longer and longer if you have a lopsided tree structure. It also possible that when queue_children was 1024 in length and did not delimit properly, it probably went into some infinite loop trying to terminate properly and never reached the condition when the LENGTH(queue_children) would be 0.

THING #2

GROUP_CONCAT()'s maximum length is limited by group_concat_max_len (default is 1024). Try expanding group_concat_max_len to something ridiculously large (5000000000).

THING #3

Increase your thread_stack, perhaps 512K or 1M. This is needed for Stored Procedures with either recursive operations or, in this case, longer local variables to make room for.