Record inheritance in mySQL

Question

I have been struggling with this for a little while.

I want to have a set of calendar records. The top level will apply to a large group; the next level will apply to a sub-set and the final level could apply to a single person.

The top level might have a record that looks like this:

ID : LinkID : Date     : Venue         :  Training       : Group
1  : NULL   : 26/2/16  : Running Track :  4 x 800m       : AC Seniors

The next level might have a record that looks like this:

ID : LinkID : Date     : Venue         :  Training       : Group
2  : 1      :          :               :  5 x 1000m      : AC Elite Men

The bottom level could look like this:

ID : LinkID : Date     : Venue         :  Training       : Group
3  : 2      :          : Local park    :  5 x 1000m      : Jon Runner

The idea is that the coach can set up a general training programme (top level) that is then edited for a sub group (middle level) and then individualised.

Jon Runner would see this record:

ID : LinkID : Date     : Venue         :  Training       : Group
3  : 2      : 26/2/16  : Local park    :  5 x 1000m      : Jon Runner

If any of the higher level information changes, then there is a need to flag further down the tree.

I have a good idea how to do this in Object Oriented Classes, but struggle to see how to implement it so that it can be stored in a database.

Any ideas or tips would be great.

Answer 1

If you want to store it as a hierarchical structure, the pattern that would best serve you is the "Closure Table" pattern, which allows for subtree operations (select/update/delete all descendants of x).

A generic example (closure table c and data table d) of what flagging an entry and all children at all levels below it would look like this:

UPDATE d 
INNER JOIN c ON c.descendant_id = d.id
SET d.flag=1 
WHERE c.ancestor_id = @thetopmostdesiredentry;

Here are some well written resources on how to implement that:

https://www.percona.com/blog/2011/02/14/moving-subtrees-in-closure-table/ https://coderwall.com/p/lixing/closure-tables-for-browsing-trees-in-sql http://karwin.blogspot.com/2010/03/rendering-trees-with-closure-tables.html

The best place to read about it is Bill Karwin's book "SQL Anti-patterns", which details it as an alternative to less savory patterns.

Answer 2

"but struggle to see how to implement it so that it can be stored in a database" -- Keep in mind that a database is a repository for data. Period, fullstop. It is very clumsy at doing anything OO.

You could build a "hierarchical" structure -- Jon is a "child" of level2, which is a "child" of ... That is done by a simple many:1 mapping wherein each row has an id and a parent which is the id of some other row in the table. (The 'root' would have 0 or NULL for parent.)

But, your application code would have to walk up (or down) that hierarchy tree to find all the records relevant to "Jon" and stitch the info together.

Answer 3

This is an alternative solution that is similar to Rick James' answer, but with a way to avoid app walking, and enjoy a fixed number (1 or 3) of statements to UPDATE the entries in all 3 applicable levels:

Create a table for each of your well-defined levels of event (I will call them top, middle, and bottom to keep this generic), so that bottom has field bottom.middle_id, and middle has field middle.top_id.

If your case remains simple, you may be able to enjoy MySQL's ability to update more than one table simultaneously, and require only ONE statement:

UPDATE `bottom` AS B
INNER JOIN `middle` AS M ON M.id = B.`middle_id`
INNER JOIN `top` AS T ON T.id = M.`top_id`
SET B.`flag`=1, M.`flag`=1, T.`flag`=1
WHERE T.`id`=@therelevantentry_id;

Otherwise, you will always only need 3 statements to select/update/delete all rows relevant to an entry in top:

UPDATE `top` AS T 
SET T.`flag`=1 
WHERE T.`id`=@therelevantentry_id;

UPDATE `middle` AS M 
INNER JOIN `top` AS T ON T.id = M.`top_id`
SET M.`flag`=1
WHERE T.`id`=@therelevantentry_id;

UPDATE `bottom` AS B
INNER JOIN `middle` AS M ON M.id = B.`middle_id`
INNER JOIN `top` AS T ON T.id = M.`top_id`
SET B.`flag`=1
WHERE T.`id`=@therelevantentry_id;