I have been absent from updating this site and my Cub Scout repo for a variety of reasons. First, I have been working on the database structure, which has been very complicated and not linked to Github. Second, I recently started a new job (not coding) that takes significant amount of my time. Third, there was a reroofing project that I had to do at home while the weather is good. The roofing project is complete and I plan on getting back to updating more frequently.
The database structure for the Cub Scout project has been an incredible learning experience. It is complex and has pushed my understanding of MySQL to new heights. I still have a ways to go before I really understand how to make it work the way I want. I hope to have the structure built in the next few weeks so I can focus on the PHP required to make the site work the way I want.
As a summary of the database project, I am building a site that will allow me to track badge progress for my son’s Cub Scout pack. The requirements for each badge have a number of required adventures and optional adventures. Each adventure has one or two layers of either mandatory or elective tasks. The challenge is to structure the database in a way that these all work together and I can minimize the PHP code. I want to have code that is completely independent of any future changes in the program. That requires the database to contain information that keys the code to execute mandatory or elective tasks and adventures in a consistent manner. I looked at two techinques and decided on the following:
Each layer will have a table. There is a table with all of the badges. Then there is an adventure table, a task table, and a subtask table. Each of these tables contain the number of required elements from the table below it. With consistent column names, positions, and purposes, I should be able to use the same PHP code to analyze the table and pull the appropriate data for display.
I still have to create the tables for storing progress. I have some suggestions on how to do that, but I need to implement those suggestions and learn how to utilize those in tracking progress.
This has been an excellent learning environment and one that I hope is practical and utilized far into the future.