DB design question: aliasing client email addresses

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!

I’d think it just a matter of not using user email addresses as identifiers in your app. The email address would map to a separate user identifier, then that separate user identifier would be used throughout your app where you need to identify the user.

Where you previously might have had an Email column in your user row to contain the users one email address, now you might have an Emails column of type EnumList to contain the possibly-several addresses.

You’d probably want a security filter and/or slice to present only the current user’s user row.