[Bodington-developers] alias_entries - comments please

[Jon M. <jo...@te...>]

My last long message got through so here the one I actually wanted to 
send again....

Comments welcome...

I'm doing some work for Leeds on user management. I'm wondering whether 
we can loosen the constraints on the alias_entries table. Specifically 
delete this one:
CONSTRAINT uq_alias_entries_1 UNIQUE (alias_id, user_id)
which prevents a user_id appearing more than once for a particular type 
of alias. The reason for this is the following;

At Leeds payroll number is used as an alias for staff users but we have 
found that staff sometimes change payroll number and because the data 
feed to Bodington doesn't identify this a new account is created for the 
same person.  We now have access to a data point called SAP Company ID 
which always stays the same for a member of staff and can be referenced 
to the payroll number. So, we would like to use a new alias 'company ID' 
as the primary alias for staff but we also want to merge accounts that 
belong to the same person and we want to retain payroll numbers because 
these are known to the users themselves where the company ID isn't. So, 
I'd like to be able to have multiple payroll numbers for a single user 
stored in the alias_entries table but at present that would break the 
constraints.  The change would also involve debugging all functionality 
in Bodington that involves aliases in case the change makes it break.

(In fact all of the above also applies to students too since Leeds has 
legacy data where students have been assigned more than one student ID 
but there is another new identifier which is guaranteed unique.)

The alternative is to use the alias_entries tables to store the most 
recently applied payroll number or student number and to store the out 
of date IDs somewhere else - possibly a Leeds specific table.

Thoughts?