Best way to update in a db a list of mails

Question

I have the following db schema.

SQL> describe USERS;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 user_id                                   NOT NULL NUMBER(38)
 name                                      NOT NULL VARCHAR2(50)
 password                                  NOT NULL VARCHAR2(50)
 score                                              NUMBER(38)
 notify_before_expiration                           NUMBER(38)
 is_admin                                  NOT NULL NUMBER(1)

SQL> describe EMAIL;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 email_id                                  NOT NULL NUMBER(38)
 email                                     NOT NULL VARCHAR2(50)
 user_id                                   NOT NULL NUMBER(38)

So one user has many emails. Users may access a form where they can add/delete theirs email. The question is: Which is the better way to update the db having the list of mails gotten from this form?

I was thinking something like: (java pseudo code)

//List of mails in db.
ArrayList<String> emailsInDB = getAllMailsFromDB(user);

//List of mails from the form
ArrayList<String> emailsInForm = form.getAllMails();

//Iterates all emails gotten from the form.
for (String email : emailsInForm) {
  if (emailsInDB.contains(email) {
    //Already in the db
    emailsInDB.remove(email, user);
  }  else {
    //New mail! Add it in the db!
    db.insertMail(email, user);
}

//All emails that were in the db, but not in the form,
//were deleted. Delete them from the db.
for (String email : emailsInDB) {
  db.deleteMail(email);
}

Better ideas are welcomed! Thanks for reading.

Answer 1

One optimization you could make is to delete emails first before inserting new emails

in pseudo SQL:

DELETE from emails WHERE emali.user_id=userid AND email NOT IN(...list of emails from the form...)

That deletes all the emails that need to be deleted in one call to the SQL server which saves a bit of latency if you have a lot of emails and the server is far away.

then proceed with inserting emails

//List of mails in db.
ArrayList<String> emailsInDB = getAllMailsFromDB(user);

//Iterates all emails gotten from the form.
for (String email : emailsInForm) {
  if (!emailsInDB.contains(email) {
    //New mail! Add it in the db!
    db.insertMail(email, user);
}

Answer 2

Although not well documented, Oracle allows for you to define a type of array called VARRAY. with these you can pass into a procedure both the 'emailsToAdd' and 'emailsToDelete' in a batch. This will allow you to reduce the amount of calls you make to the DB, improving overall performance of the method. Here is an example to get you started.