2

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.

2

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.

  • Cool. But note that it requires extra, redundant, rows in the table. – Rick James Jan 28 '16 at 1:00
  • You would have more rows (the linkage rows in the closure table) this way, but in exchange, we are now flexible for adding new tiers to N depth (and things like group nesting). It may also be advantageous to have only 2 tables (instead of the 3 or N we would have otherwise). – Christopher McGowan Jan 28 '16 at 1:20
1

"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.

  • Using the closure table pattern, the walking by the app would not be necessary, and a subtree (all descendants of x) can be specified in a single query. – Christopher McGowan Jan 28 '16 at 0:16
  • @ChristopherMcGowan - Please share how to do it in a single query. – Rick James Jan 28 '16 at 0:20
  • UPDATE d INNER JOIN c ON c.descendant_id = d.id SET d.flag=1 WHERE c.ancestor_id = @thetopmostdesiredentry; – Christopher McGowan Jan 28 '16 at 0:34
  • By the way, what you suggest is still fine for the example in this question since it is fixed depth, and each level is clearly defined. In that case, you always only need 3 queries (one for each level) and the update joins the level(s) above it with the same criteria as the first level. So no walking necessary there either, but more than 1 query for using that pattern instead. – Christopher McGowan Jan 28 '16 at 0:43
  • The 3 queries could be put into 1 by using UNION. – Rick James Jan 28 '16 at 1:01
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.