I'd appreciate some tips on how to design required sets and constraints for an activity signup database (web based).
Background: The YMCA Adventure Guides allows groups (circles, which in turn are part of larger groups called expeditions )of dads and their kids (people) to get together and do fun things (activities).
The attached diagram shows the relationships:
http://home.pacbell.net/eulimi/yag_erd.GIF
Each person can be a member of one or more circles (each kid belongs to one circle. Each dad can belong to 2 or more cirlces if the dad has more than one kid in the program.) Each circle belongs to one expedition. The administrator associates several allowed activities to each expedition. Each activity could be associated with more than one expedition, hence the activity-exped table. Each person can sign up with one or more activities. Each activity can, of course, be participated in by more than one person, hence the signup table.
When the user selects a record in the person table, the user can then sign-up the person for one or more activities (in a "child" signup table that appears). My question is: How do I constrain the list of allowed activities for a person based on that person's membership in the expedition? For example, the girls expedition is called Atlantis. The Atlantis record in the expedition table is associated with an activity called the Sweetheart Dance (a Valentines father/daughter event) in the activity table. So, if a person (girl) is a member of a circle that's a member of Atlantis, I'd like the user to be able to sign up the girl (and her dad) for the Sweetheart Dance via the signup table. Boys, on the other hand, are members of the Northstar expedition. Northstar wouldn't, of course, be associated with the Sweetheart Dance. So, if the user is signing up a boy for an activity, the Sweetheart Dance shouldn't appear as an option. (The boy's father could sign up for the dance IF the boy has a sister, who'd be in a girls' circle.)
I imagine that I'd have to make a multi-level set to impose signup constraint rules, but I'm not sure if this is the best approach. Note that I'd eventually like to present the user with a series of checkboxes next to allowed activities for each person. Check the box to attend, leave unchecked if not attending.
Thanks very much for your ideas!
-Kurt
Background: The YMCA Adventure Guides allows groups (circles, which in turn are part of larger groups called expeditions )of dads and their kids (people) to get together and do fun things (activities).
The attached diagram shows the relationships:
http://home.pacbell.net/eulimi/yag_erd.GIF
Each person can be a member of one or more circles (each kid belongs to one circle. Each dad can belong to 2 or more cirlces if the dad has more than one kid in the program.) Each circle belongs to one expedition. The administrator associates several allowed activities to each expedition. Each activity could be associated with more than one expedition, hence the activity-exped table. Each person can sign up with one or more activities. Each activity can, of course, be participated in by more than one person, hence the signup table.
When the user selects a record in the person table, the user can then sign-up the person for one or more activities (in a "child" signup table that appears). My question is: How do I constrain the list of allowed activities for a person based on that person's membership in the expedition? For example, the girls expedition is called Atlantis. The Atlantis record in the expedition table is associated with an activity called the Sweetheart Dance (a Valentines father/daughter event) in the activity table. So, if a person (girl) is a member of a circle that's a member of Atlantis, I'd like the user to be able to sign up the girl (and her dad) for the Sweetheart Dance via the signup table. Boys, on the other hand, are members of the Northstar expedition. Northstar wouldn't, of course, be associated with the Sweetheart Dance. So, if the user is signing up a boy for an activity, the Sweetheart Dance shouldn't appear as an option. (The boy's father could sign up for the dance IF the boy has a sister, who'd be in a girls' circle.)
I imagine that I'd have to make a multi-level set to impose signup constraint rules, but I'm not sure if this is the best approach. Note that I'd eventually like to present the user with a series of checkboxes next to allowed activities for each person. Check the box to attend, leave unchecked if not attending.
Thanks very much for your ideas!
-Kurt
Comment