Need help with schema design: creating one-to-many relationship through forms

I developed a base for a simple one-time community program that is now being expended into multiple cycles. I think I have unnecessary tables and manual tasks, and am missing some elements that would help a lot.

Here is the basic info (I am new to this community so not sure what is helpful vs excessive)

  • The base manage volunteer participants in a large community program.
  • People signup on a rolling basis.
  • People sign up and then have options:
    – Just be on the mailing list
    – Attend date-specific, one-time events. Goal is to record who registers and who attends (data from Zoom)
    – Sign up for a training
    – Sign up to join a group

Current design:

  • People table, with form for person-level info (contact, demos, etc). Submit redirects to second form, in…
  • Interest table, with form that asks for what they are signing up for.
  • John Doe signs up in Spring 2020 to attend an event. Then in Fall 2020 he signs up to join a group. This create 2 records in the People table, and 2 in the Interest table.
  • Using DeDupe and manual correcting, I remove the second People record, and create a one to many relationship between People and Interest.

Challenge:

  • This sign-up goes out every 3-4 months, and people can sign up for all/all of those options.
  • This program is about to go through it’s 3rd cycle, and I am about to send out a sign-up form again, and the manual clean up is a PITA and not scalable.

Factors:
– When a person signs up, it would be fine for them to find THEIR records and update them or add a option, but I don’t want them to see a list of all other People
– There is no way any of these folks will remember whether they have signed up already. I mean, ideally, yes, but since I can’t remember if I’ve already made coffee most days, I don’t expect much of anyone else.

I’m sure this is not enough info…but maybe a good place to stop this post!
TIA!

There’s a service for doing this well - MailChimp.

We often strive to compress services into a unified model without fully grasping the devils in those details.

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.