We lost our database administrator over a year ago - and even then she was promoted into the job internally and sort of learned on the job. Basically I say this to establish that strong data standards have not been enforced for a long time. We have a company that set up and administers our database - but they are particularly unhelpful and different people give different answers so I am really just looking for some best practices and maybe some key things that I should be asking for so that I get their attention and we are on the same page.
Our biggest problem is, in my opinion, that we have many many duplicate records. People make new records when they donate, they make new records when they sign up for email newsletters, they make new records when they sign up on our new app...etc etc. This causes problems down the line because then our website returns errors when it finds multiple profiles with the same email and won't let people buy things/register etc. - and we waste money mailing to the same people several times. We really need to tackle this problem - especially for our certified tree farm members because they are getting tired of getting multiple emails and mailings plus we need an accurate count for real programmatic reasons. I know I'm asking a big question - but how SHOULD these things be set up? I know there must be a better way because other websites seem capable of handling these issues fine. For instance - if we have a husband and wife - should they each have their own profile or share one? What if they share an email address (our population is a bit older so many do) right now we can create a 'shared email' relationship - is that a real thing or is there another way to do this?
I know it would be impossible to give a really specific answer - I'm just looking for a gold standard of functionality so I know what we should be aiming for and what we should be able to do. I don't really trust this company because they tell us things are impossible, but I see them working well all around me.
Thank you for any advice!