[OPEN-ILS-DEV] Normalization of e-mail addresses and phone numbers in patron data

[Lebbeous Fogle-Weekley]

This is something I have been planning to undertake.  In light of a 
conversation that took place in #evergreen about doing new things with 
actor.usr_address, in which email addresses and phone numbers were also 
at mentioned, I think I'd better share my plans and try to get on the 
same page with anyone else who's going to make changes in that neighborhood.

The actor.usr table has four columns which I would like to break out 
into three new tables.

This would give us the ability to add metadata to phone numbers and 
email addresses (I'm particularly interested in a "validity" property 
for them) without bloating the actor.usr table.  We'd also get the 
ability to have multiple email addresses for patrons, and more free 
organization of phone numbers for patrons (instead of having a hard 
three types: day, evening, other).

The email column should be replaced with one new table (actor.usr_email) 
and a set of relationships, and the day_phone, evening_phone and 
other_phone columns should be replaced with a second table 
(actor.usr_phone) and set of relationships.

The third table (actor.usr_phone_type) is the target of a particular 
foreign key column in actor.usr_phone.

New table: actor.usr_email

id, a primary key
usr, a foreign key referring to a row in actor.usr
address, a text field, not nullable
label, a text field, nullable
valid, a boolean defaulting to true
notify, a boolean defaulting to false

Plan: You might imagine I'd eliminate the email field from the actor.usr 
table now, but I actually prefer to keep it, but change it into a 
foreign key pointing back at actor.usr_email.  This creates a set of 
circular references, true, but the purpose of the one pointing from 
actor.usr to actor.usr_email is to define which of potentially many rows 
in actor.usr_email should be considered primary to the user.  This is 
the same idea behind the card column of actor.usr the way it works now.

To consider: We may still wish to have a constraint on the foreign key 
represented in the actor.usr.email field, such that it cannot refer to a 
row in actor.usr_email with a different usr value.

To consider: The plan above precludes the possibility of keeping the 
email field around as a virtual field in the IDL, stuffing it in middle 
layer methods so that some interfaces can keep using it without changes. 
  To have such a virtual field now, we'd need to give the actor.usr -> 
actor.usr_email linking column a different name (such as primary_email).

New table: actor.usr_phone

id, a primary key
usr, a foreign key referring to a row in actor.usr
phone_type, a foreign key referring to a row in actor.usr_phone_type
label, a text field, nullable
number, a text field, not nullable
valid, a boolean field, default true
notify_voice, a boolean field, default false
notify_sms, a boolean field, default false

To consider: Phone numbers could, and maybe should, have time windows 
for notifications associated with them.  There could be defaults based 
on whether a phone number originally came from day_phone or 
evening_phone.  More to discuss.

New table: actor.usr_phone_type

code, a primary key
label, a text field with internationalization, not nullable

To consider: One could argue that actor.usr_phone_type is not necessary 
at all, and I could be so persuaded, but I think it's the logical way to 
preserve distinctions among the existing day_phone, evening_phone and 
other_phone fields.

I'm not sure we need a "primary" phone for each user in the same way 
that we need a "primary" email address.  For that reason I'm not 
specifying a foreign key on actor.usr that refers to actor.usr_phone to 
indicate a "primary" or otherwise special phone number.  We can revisit 
this point if somebody can point out why we would, in fact, need a 
primary phone number.  Otherwise, the existing day_phone, evening_phone 
and other_phone fields in the IDL can be redefined as virtual, and 
helpfully stuffed by middle layer methods when possible.

Middle layer changes

Fortunately the Fieldmapper class for actor.usr does not have the pcrud 
controller, so we don't have to go out and find code using pcrud to 
fetch users and teach such code any new tricks.  Within 
OpenILS::Application::Actor, we can change the subroutine flesh_user() 
to 1) flesh the new email and phone objects by their has_many IDL links, 
and 2) stuff any virtual fields we're going to use with the best-fit 
data from the new tables. E.g., 
$user->day_phone($user->phones()->[$some_element_chosen_deterministically]).

Ideally that will make many interfaces that retrieve user data able to 
continue along as if nothing has changed, until there is a particular 
need to let those interfaces know anything has changed.

Testing should tease out other areas of the middle layer where changes 
will need to be made. flesh_user() is certainly not the only subroutine 
that handles user objects, and other code will be affected by the 
database schema changes.

User Editor changes

We will need to adjust the current User Editor to deal with these schema 
changes, naturally, and we should particularly remember to make sure 
that the toggle for the validity of an e-mail address or phone number is 
sensibly placed and functional.

Notifications

All action trigger event definitions with the SendEmail reactor will 
need updated to get the complete set of valid and notifiable email 
addresses per user.  A TT helper method may be appropriate.

All action trigger event definitions for telephone notices will need 
similar updating for phone numbers.

I have no designs for changing the way ahr.notify_phone is used, for now.

OPAC

"My Account" in the Template Toolkit OPAC should allow patrons to edit 
their email addresses and mark their validity, but it should not allow 
them to edit their phone numbers (sometimes you have to send people to 
collections, after all).

Possibly in the future it could offer patrons the ability to add new 
phone numbers for notifications (maybe SMS numbers should work this way, 
and we bring them in out of usr_setting land? Not yet sure whether that 
would actually be an improvement or if it just "seems" cleaner), but we 
do not want patrons to be able to edit their already known phone numbers 
(remember, these may be used in collections).

Other concerns

To be clear, I'm not proposing anything that will automatically 
determine the validity of patron email addresses or phone numbers, but 
that's not to say that can't come later.

-- 
Lebbeous Fogle-Weekley
  | Software Developer
  | Equinox Software, Inc. / Your Library's Guide to Open Source
  | phone:  1-877-OPEN-ILS (673-6457)
  | email:  lebbeous at esilibrary.com
  | web:  http://www.esilibrary.com