The starting point would be for you to create a visualization of the data, so that it is easy to understand the relationship between the tables, based on your business needs.
Looking only at what you put. It would start like this
1. Contact Table (The actual users)
2. Group Table (The possible Groups)
2a. Group Type Table
Note: Since you have different types of Groups, you COULD just create a local Group Table Option Set, or create a Global Option Set.
For me... while this is good, I prefer to use a simple Text best mapping table
This table would hold
-Type Id
-Type Namne
-Type Description
Then in your Form, you would have a dropdown or combobox that is full of these string values, in the Group Table, you can either
a) copy the string (making analytics easy)
b) a lookup between 2 and 2b. Meh for me.. I do not like having tons of lookups even though they do help with relationship management
3. Attendance (The Details about the Groups a person was in)
This is essentially a mapping table between the other 2 tables
You can have the following for instance
-Contact Id (LookUp)
-Group Id (LookUp)
-(possible) Schedule Id (lookup)
-Date of Attendance (And / Or)
--You could intentionally break down Date into
Week (Int)
Month (Int)
Year (Int)
The benefit to breaking it down, and not just having the data, but it makes SUMing and create calculations etc even in Power BI easier.
4. Schedule
Not sure if you need a sched, but if the same group can happen more than once, then you need to map a Group + Schedule with the Attendance
Now that all being said, this is a very Data driven activity and something that really should go to a Consultant.
