0

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?

3

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
  • sure, but how do you get the key value? a username? then how do you change the user name? – James Jul 16 at 14:36
  • 1
    Internally the person/contact would have a immutable candidate key value which you would use for all lookups where possible. To find this value at any point that your application does not know it, it would be looked up other properties like username. The username can change, but if you make sure everything internally refers to the surrogate key this will not be an issue. – David Spillett Jul 16 at 14:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.