Re: [Bodington-developers] alias_entries - Suggested change to constraints.

[Matthew B. <mat...@ou...>]

Jon Maber wrote:
> 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.

There are two ways to extend aliases:

- The one you are suggesting to allow a user to have more than one entry 
for an alias.

- Allow an alias to be had for more than one user, so for example you 
could have an alias of unit with multiple people having the alias entry 
of biology. In most cases this information is better captured in groups 
although it may be useful to have the original data that was used to 
calculate the groups in Bodington.

Possible way of solving the multiple aliases per user:

Could you superclass  Alias and AliasEntry to MultiAlias and 
MultiAliasEntry. These classes don't enforce the restriction of only 
allowing one alias per user. This way any code that deals with aliases 
will never get more than one entry per user and programmer won't have to 
change how they thing about aliases but functionality in certian places 
can be alter to work with MultAlias(Entry).

-- 
  -- Matthew Buckett, VLE Developer
  -- Learning Technologies Group, Oxford University Computing Services
  -- Tel: +44 (0)1865 283660 http://www.oucs.ox.ac.uk/ltg/