Holy Cow! How do I handle a many to many to many to many relationship?

[Guy Smith]

My three remaining brain cells just went AWOL trying to resolve this one:

I am building a database for a Boy Scout Troop (I'm the new Treasurer) and have much of it up and running, but can't figure out how to properly relate the following:

Many (dozens) of scouts attend many outings each throughout the year.

Each outing has many (4-5) scout patrols attending.

Each scout patrol at each outing has many scouts (8-12) in it.

Many scouts (2-3 out of each group of 8-12) in each patrol buy food for their patrol.

All scouts in each patrol pay their fair share of the food costs for their patrol.

I know how to do the calcs to divvy up the costs, but figuring out the relationships is waaaay above my brain's capacity!

I need to name each outing, list the patrols for that particular outing and which scouts are in each outing patrol and drill down one more time to put in how much the food purchasers each spent and then have the resultant costs/credits be applied to each scout's individual income/expense account.

I semi-sorta understand join tables, but can't seem to get this one right.

Any help would be very much appreciated - thanks in advance for any guidance and recommendations.

Sincerely,

Guy

[comment]

Each outing has many (4-5) scout patrols attending.

Do they come in already divided into patrols? Or do they arrive as individuals, and divide into patrols for the current outing only?

[Guy Smith]

Each scout is assigned a temporary patrol for each outing: Scout 'Joe' could be in Patrol #1 for outing A and in patrol #3 for outing B, etc.

[comment]

I would start with:

Scouts -< Attendance >- Patrols >- Outings

Create a new record in Attendance for each participating scout, and assign them to a patrol. Now, if you want to keep it very simple, you could have two fields in the Attendance table: one for the expense and one for the settlement (this would be negative in case of reimbursement).

[Guy Smith]

You rock!!!

I somehow came up with three join tables and a bunch of headaches.

Your solution is MUCH cleaner and easier - thanks very much for your insight.

[bcooney]

Have you looked at TroopLedger? As a certified FM dev and mother of a scout, I was tempted to reinvent the wheel. However, I felt that it would be better for the Troop to leave them with a commercial software solution that is supported and well-tested. Worth a look, and I wouldn't be surprised if they're already using TroopMaster.

[Guy Smith]

We already have TroopMaster and it is used extensively by the ScoutMaster, but I do not have access to a Windows machine - I've got a Mac and the program doesn't run on a Mac. Thanks for the heads-up, though.