Good morning – I’m mainly a business guy but with some IT talent as my secret sauce. I understand database normalization fairly well and have programmed software/apps/websites in several different languages over the years.
I’m facing a new challenge in my current business. It’s a counseling agency. We are first contacted by people through our website where we track them as leads using their email address as a unique identifier. They then (hopefully) schedule appointments in another system (Acuity Scheduling – great SaaS btw) where they are uniquely tracked by their email address. When we charge out their appointments I use Zapier to record a Sales Receipt in Quickbooks online where, again, the customers are uniquely tracked by their email address.
Most people start as a lead but some start as a booking in Acuity.
What I want to do is to track the individual customer/couple across their life cycle from lead to booking to billing. Where we run into some challenges is if they use a different email address partway through the process. Or if they start with, say, the husband contacting us as a lead under his email but then the wife scheduling the appointments.
In any case, I was wondering what the table/database design would need to look like where we could manually link one or more email addresses back to a single identifier for that person or couple. This is a new scenario for me and I’d appreciate your input!