What's best practices for changing a user's email in an application? I'm working on a crud for an email application, and I'd like the ability to change "everything". Right now, however, it matches the JSON string to the Database based on the email address.

Problem: Under this model, a user can never change their email. A new email address = a new user.

    declare @email varchar(255),
    @ID int

    insert into announcement_jsonlog (json_string)
    values (@json)

    select @email = email from 
            openjson(@json)
            WITH
            (email varchar(255)         '$.Email')


    insert into announcement_contacthistory (   
    ID, Email, Prefix, FirstName, MiddleInitial, LastName, Suffix, Title, Company, Address1, Address2, City, State, Zip, Zip_4, Phone, Extension, Unregistered, AddDateTime, LModifiedDateTime, 
    longkey, shortkey, History_addDateTime)

    select *, current_timestamp from announcement_contact
    where email = @email


Question: how would you design a CRUD that lets you change the email address, too?
It sounds like you are using the email address as a key, which is not good practise. Anything that can change is not a good candidate to be a key value which includes email addresses and phone numbers. Other reasons it is not a good idea include:


It could be NULL: maybe there are people in your target audience who don't use email so won't have an address?
Someone could have more than one address/number
People might share an address/number so it is not unique


If you do not have a non-changing, unique, never-NULL value that is therefore a candidate to be the key value, it is usual to have a generated surrogate key: most commonly an integer value or UUID that is decided when the record is created and does not depend on any properties of the record. You appear to have such a key already in your announcement_contacthistory table, the ID column.

This is particularly important if the key value is referenced by foreign key values in other tables as appears to be the case here. While SQL Server and Azure SQL DB have ON UPDATE CASCADE which makes changing such referred values possible and easy from the coders point of view (just update the primary value, the engine will go away and fix up all the references) this can be very inefficient resulting in a lot of related changes being made. If announcement_contacthistory had a reference to announcement_contact.id instead of announcement_contact.email you could change the email of a user without needing to update all the references in announcement_contacthistory and elsewhere.

Of course you may want to keep a history of email addresses so you know what was used at the point in time the messages was sent, either by still recording the address in the message record or by keeping a history of the values in the contact table (perhaps using a system versioned temporal table).

To find info on the relevant theory and practise, search around the following terms:


Key
Candidate Key
Surrogate Key
Primary Key
Unique Key