mySQL database project.

That should work. And to update, I could search by the appropriate subset, delete the rows, and add the new ones.

Hey, you're welcome...

I will have to really digest your answer. The numerous variables makes this a very interesting problem and a real stretch for me with what I know. I am learning a ton.

I was thinking about this and thought that maybe use PHP to create a table (if it doesn't already exist) with columns created from the badge requirements and columns and using the scout id as its rows. Then the program creates any necessary table for the badge/scout completion. The scout table has columns for each badge that update with the % complete for the badge based on the entries stored in the badge/scout table. The status of each requirement is stored in the badge/scout table.

Here is the description for the Tiger badge (for 1st graders). They have to do all of the required adventures and one elective. I particularly like it when they say do items 1-3 and one of 4-6. And #5 has complete a and 3 of b through g.

TIGER REQUIRED ADVENTURES

Tiger Adventure: Backyard Jungle

1. Take a 1-foot hike. Make a list of the living things you find on your 1-foot hike.
2. Point out two different kinds of birds that live in your area.
3. Be helpful to plants and animals by planting a tree or other plant in your neighborhood.
4. Build and hang a birdhouse.
5. With your adult partner, go on a walk, and pick out two sounds you hear in your “jungle.”

Tiger Adventure: Games Tigers Play

1. Do the following: a. Play two initiative or team-building games with the members of your den. b. Listen carefully to your leader while the rules are being explained, and follow directions when playing. c. At the end of the game, talk with the leader about what you learned when you played the game. Tell how you helped the den by playing your part.
2. Make up a game with the members of your den.
3. Make up a new game, and play it with your family or members of your den or pack.
4. While at a sporting event, ask a player or coach why he or she thinks it is important to be active.
5. Bring a nutritious snack to a den meeting. Share why you picked it and what makes it a good snack choice.

Tiger Adventure: My Family’s Duty to God Complete requirement 1 and at least two from requirements 2–4.

1. With your adult partner, find out what duty to God means to your family.
2. Find out what makes each member of your family special.
3. With your family, make a project that shows your family’s beliefs about God.
4. Participate in a worship experience or activity with your family.

Tiger Adventure: Team Tiger

1. List the different teams of which you are a part.
2. With your den, make a den job chart that shows everyone doing something to help. As one of the den jobs, lead the Pledge of Allegiance at a den meeting.
3. Pick two chores you will do at home once a week for a month.
4. Make a chart to show three ways that members of your Tiger team are different from each other.
5. Do an activity to help your community or neighborhood team.

Tiger Adventure: Tiger Bites

1. Identify three good food choices and three foods that would not be good choices.
2. Show that you know the difference between a fruit and a vegetable. Eat one of each.
3. With your adult partner, pick a job to help your family at mealtime. Do it every day for one week.
4. Show you can keep yourself and your personal area clean.
5. Talk with your adult partner about what foods you can eat with your fingers. Practice your manners when eating them.
6. With your adult partner, plan and make a good snack choice or other nutritious food to share with your den.

Tiger Adventure: Tigers in the Wild

1. With your adult partner, name and collect the Cub Scout Six Essentials you need for a hike. Tell your den leader what you would need to add to your list if it rains.
2. Go for a short hike with your den or family, and carry your own gear. Show you know how to get ready for this hike.
3. Do the following: a. Listen while your leader reads the Outdoor Code. Talk about how you can be clean in your outdoor manners. b. Listen while your leader reads the Leave No Trace Principles for Kids. Discuss why you should “Trash Your Trash.” c. Apply the Outdoor Code and Leave No Trace Principles for Kids on your Tiger den and pack outings. After one outing, share what you did to demonstrate the principles you discussed.
4. While on the hike, find three different kinds of plants, animals, or signs that animals have been on the trail. List what you saw in your Tiger Handbook.
5. Participate in an outdoor pack meeting or pack campout campfire. Sing a song and act out a skit with your Tiger den as part of the program.
6. Find two different trees and two different types of plants that grow in your area. Write their names in your Tiger Handbook.
7. Visit a nearby nature center, zoo, or another outside place with your family or den. Learn more about two animals, and write down two interesting things about them in your Tiger Handbook.

TIGER ELECTIVE ADVENTURES Tiger Elective Adventure: Curiosity, Intrigue, and Magical Mysteries

1. Learn a magic trick. Practice your magic trick so you can perform it in front of an audience.
2. Create an invitation to a magic show.
3. With your den or with your family, put on a magic show for an audience.
4. Create a secret code.
5. With the other Scouts in your den or with your family, crack a code that you did not create.
6. Spell your name using sign language, and spell your name in Braille.
7. With the help of your adult partner, conduct a science demonstration that shows how magic works.
8. Share what you learned from your science demonstration.

Tiger Elective Adventure: Earning Your Stripes

1. Bring in and share with your den five items that are the color orange.
2. Demonstrate loyalty over the next week at school or in your community. Share at your next den meeting how you were loyal to others.
3. With your adult partner, decide on one new task you can do to help your family, and do it.
4. Talk with your den and adult partner about polite language. Learn how to shake hands properly and introduce yourself.
5. Play a game with your den. Then discuss how your den played politely.
6. With your adult partner and den, work on a service project for your pack’s meeting place or chartered organization.

Tiger Elective Adventure: Family Stories

1. Discuss with your adult partner and/or family where your family originated. Discuss their history, traditions, and culture—your family heritage. Share a story or bring something to share with your den about yourself and your family.
2. Make a family crest.
3. Visit your public library to find out information about your heritage.
4. Interview one of your grandparents or another family elder, and share with your den what you found.
5. Make a family tree.
6. Share with your den how you got your name or what your name means.
7. Share with your den your favorite snack or dessert that reflects your cultural heritage.
8. Learn where your family came from, and locate it on a map. Share this information with your den . With the help of your adult partner, locate and write to a pen pal from that location.

Tiger Elective Adventure: Floats and Boats

1. Identify five different types of boats.
2. Build a boat from recycled materials, and float it on the water.
3. With your den, say the SCOUT water safety chant.
4. Play the buddy game with your den.
5. Show that you can put on and fasten a life jacket the correct way.
6. Show how to safely help someone who needs assistance in the water, without having to enter the water yourself.
7. Show how to enter the water safely, blow your breath out under the water, and do a prone glide.

Tiger Elective Adventure: Good Knights

1. Do the following: a. With your den or adult partner, say the Scout Law. Explain to your den one of the 12 points of the Law and why you think a knight would have the same behavior. b. If you have not already done so, make a code of conduct with your den that will describe how each person should act when you are all together. If your den has a code of conduct, discuss with your den the updates it might need. Vote on which actions should go in your den code of conduct.
2. Create a den shield and a personal shield.
3. Using recycled materials, design and build a small castle with your adult partner to display at the pack meeting.
4. Think of one physical challenge that could be part of an obstacle course. Then help your den design a Tiger knight obstacle course. With your adult partner, participate in the course.
5. Participate in a service project.

Tiger Elective Adventure: Rolling Tigers

1. With your den or adult partner, discuss two different types of bicycles and their uses.
2. With your den or adult partner, try on safety gear you should use while riding your bike. Show how to wear a bicycle helmet properly.
3. With your den or adult partner, learn and demonstrate safety tips to follow when riding your bicycle.
4. Learn and demonstrate proper hand signals.
5. With your den or adult partner, do a safety check on your bicycle.
6. With your den or family, go on a bicycle hike wearing your safety equipment. Follow the bicycling safety and traffic laws.
7. Learn about a famous bicycle race or famous cyclist. Share what you learn with your den.
8. Visit your local or state police department to learn about bicycle-riding laws.
9. Identify two jobs that use bicycles.

Tiger Elective Adventures: Sky Is the Limit

1. With your den or adult partner, go outside to observe the night sky. Talk about objects you see or might see.
2. Look at a distant object through a telescope or binoculars. Show how to focus the device you chose.
3. Observe in the sky or select from a book or chart two constellations that are easy to see in the night sky. With your adult partner, find out the names of the stars that make up the constellation and how the constellation got its name. Share what you found with your den.
4. Create and name your own constellation. Share your constellation with your den.
5. Create a homemade constellation.
6. Find out about two different jobs related to astronomy. Share this information with your den.
7. Find out about two astronauts who were Scouts when they were younger. Share what you learned with your den.
8. With your den or family, visit a planetarium, observatory, science museum, astronomy club, or college or high school astronomy teacher. Before you go, write down questions you might want to ask. Share what you learned.

Tiger Elective Adventures: Stories in Shapes

1. Visit an art gallery or a museum, explore an art website, or visit your library. Do each of the following: a. Look at pictures of some abstract art with your den or family. Decide what you like about the art, and share your ideas with the other Tigers. b. Create an art piece.
2. Do the following: a. Draw or create an art piece using shapes. b. Use tangrams to create shapes.

Tiger Elective Adventure: Tiger-iffic! Complete 1–3 and one from 4–6.

1. Play at least two different games by yourself; one may be a video game.
2. Play a board game or another inside game with one or more members of your den.
3. Play a problem-solving game with your den.
4. With your parent’s or guardian’s permission: a. Play a video game with family members in a family tournament. b. List at least three tips that would help someone who was learning how to play your favorite video game. c. Play an appropriate video game with a friend for 30 minutes.
5. With other members of your den, invent a game, OR change the rules of a game you know, and play the game.
6. Play a team game with your den.

Tiger Elective Adventure: Tiger: Safe and Smart

1. Do the following: a. Memorize your address, and say it to your den leader or adult partner. b. Memorize an emergency contact’s phone number, and say it to your den leader or adult partner. c. Take the 911 safety quiz.
2. Do the following: a. Show you can “Stop, Drop, and Roll.” b. Show you know how to safely roll someone else in a blanket to put out a fire.
3. Make a fire escape map with your adult partner.
4. Explain your fire escape map, and try a practice fire drill at home.
5. Find the smoke detectors in your home. With the help of your adult partner, check the batteries.
6. Visit an emergency responder station, or have an emergency responder visit you.

Tiger Elective Adventures: Tiger Tag

1. Choose one active game you like, and tell your den about it.
2. Do the following: a. Play two relay games with your den and your adult partner. b. Tell your adult partner or the other Tigers what you liked best about each game. c. Have your den choose a relay game that everyone would like to play, and play it several times.
3. With your adult partner, select an active outside game that you could play with the members of your den. Talk about your game at the den meeting. With your den, decide on a game to play.
4. Play the game that your den has chosen. After the game, discuss with your den leader the meaning of being a good sport.

Tiger Elective Adventures: Tiger Tales

1. Create a tall tale with your den.
2. Create your own tall tale. Share your tall tale with your den.
3. Read a tall tale with your adult partner.
4. Create a piece of art from a scene in the tall tale you have read, using your choice of materials. Share it with your den.
5. Play a game from the past.
6. Sing two folk songs.
7. Visit a historical museum or landmark with your adult partner.

Tiger Elective Adventures: Tiger Theater

1. With your den, discuss the following types of theater: puppet shows, reader’s theater, and pantomime.
2. As a den, play a game of one-word charades with your adult partners.
3. Make a puppet to show your den or display at a pack meeting.
4. Perform a simple reader’s theater. Make a mask afterward to show what your character looks like.
5. Watch a play or attend a story time at a library.

I didn't read through your entire reply, but I think I got the idea for your system.

I would really advice against dynamically creating tables as well as creating tables for each individual badge. The best practise is always to have a static database with static tables and for each change in the system you only have to add one or more rows to the appropriate tables.

I could be of more help if you could simplify the system in your example. Can you give me an example of your system assuming there is only 2 badges and a couple of requirements / options for each badge? It's hard for me to visualize this 100% from only a textual representation of a rather complex system. There are numerous things that you call "Tiger Adventure". To get the tiger badge you have to complete each of these adventures, and for each adventure you have to complete each task?

What's the "elective adventures"?

Ok. Lets say there are two badges (there are really only 6). There is no duplication in the steps or choices in the badges.

Badge 1:

Required Task 1 (called adventures)

a. step 1 required

b. step 2 required

Required Task 2

a. step 1 required

b. step 2 complete one of two choices

a. choice 1

b. choice 2

Elective task (complete 2 of three choices)

a. choice 1

1. Step 1

2. Step 2

b. choice 2

1. Step 1

2. Step 2 (do one of the following)

A. do 1

B. do 2

c. choice 3

1. Step 1

2. Step 2

Badge 2: similar structure as badge 1, but none of the steps in badge 2 are the same as those in badge 1.

The structure of the database needs to be balanced with the PHP used to display and record progress. I thought is to design the database so that there is no updating of the PHP required when the program changes. Thus, I was thinking of a column (1 or more depending on how the PHP works out) that is called by the PHP to tell it if there are options and how many option have to be done. That way I can have limited PHP objects that are called in response to what is in the database for any badge. But we also have to know what the relationships are in the database.

This is my current progress on making the tables for each badge. It doesn't work, but I think it is close. The tables are not really dynamic I think because it only creating the table once. I guess I could run a similar code in phpMyAdmin but I don't know that I can run actual PHP. I could really just run this code once to create the tables, and then delete it. Or I could run it to echo out the create command and run the create command in phpMyAdmin to create the tables.

<?php
$reqs = requirements_db ($table); // this pulls the mandatory and optional columns from the table listing those.  That table would have columns used by the PHP to analyze the various options in each badge.
    $number = count($reqs);
    $i = 0;  // artifact from the while loop below
    $col = "";
    foreach ($reqs as $req) {
//      while ($i<$number){
            $col .= '"' . $req['mandatory'] . '"' . ' (VARCHAR 300), ';
//          $i++;
//      }
//      if ($i === $number) {
//          $col .= $req[0] . ' (VARCHAR 300)';
//      }

//      var_dump($req);
        echo '<br>' . $col . '<br><br>';
    }
    unset ($req);
try {
    $sql = "CREATE TABLE IF NOT EXISTS " . $table . "_reqs (" . $col . ")";
    $db->exec($sql);
    echo $table . "_reqs created.";

} catch (Exception $e) {
    echo $e->getMessage() . ' dbCreate badge table creation.';
    exit;
}

One problem I have with the code is there is an extra comma at the end of the columns that I don't know how to get rid of. That was the idea of the while statement, but it wasn't working. I fixed some of the problems but have not retried the while loop. The logic seems wrong with the foreach loop. Also, this code is only for a badge with set requirements and no options. I wanted to get this to work before I tackled options. It also does not have the foreign key with the Cub Scout's id.

The $table variable would have the badge table and create the new table with the badge name_reqs. That table would have scout ids and record boolean (probably) if the specific requirement was complete. the PHP would then evaluate if the badge requirements were met.

The scout table currently has First and Last names, den (used for sorting scouts into age groups), a column for each badge (intended to store % complete on the badge requirements), health issues for the scout, and money earned toward summer camp. I was going to have a separate table for parents and their contact information after considering your previous post.

The real program is described in a 43 page PDF. Clicking here will download the PDF.