From: Jon M. <jo...@te...> - 2006-07-28 21:34:56
|
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? |
From: Matthew B. <mat...@ou...> - 2006-07-31 09:45:00
|
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/ |
From: Jon M. <jo...@te...> - 2006-07-31 10:48:30
|
Matthew Buckett wrote: > 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) An ingenious idea! It might also help address Peter's comment. This way there is a mechanism that enforces a distinction between the two types of alias. Functionality that takes an alias value and finds a user can ignore the distinction but functionality that takes a user and looks up an alias entry to label him/her can present a list of optional alias types that excludes the MultiAlias objects, but depending on its purpose it can choose to return and display the list of alias entries per user. So, taking the MCQ test as an example it could be made possible to ask for 'student_id' to be printed in the table but disallow 'old_student_ids' because the former would be an AliasEntry and the latter a MultiAliasEntry. On the other hand the user directory tool could happily allow a member of staff to find a student by typing in an 'old_student_ids' value. The main purpose of the MultiAlias would be for administrative data and wouldn't necessarily contain aliases of interest to staff and student. The other advantage of subclassing is that we can implement it at Leeds with Bodington 2.6 as an add-in without changing the existing code or the definitions of the existing database tables. Give that man a pay rise! Jon |
From: Matthew B. <mat...@ou...> - 2006-08-01 08:17:15
|
Jon Maber wrote: > Matthew Buckett wrote: >> 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) > An ingenious idea! Thanks. > It might also help address Peter's comment. > This way there is a mechanism that enforces a distinction between the > two types of alias. Functionality that takes an alias value and finds a > user can ignore the distinction but functionality that takes a user and > looks up an alias entry to label him/her can present a list of optional > alias types that excludes the MultiAlias objects, but depending on its > purpose it can choose to return and display the list of alias entries > per user. So, taking the MCQ test as an example it could be made > possible to ask for 'student_id' to be printed in the table but disallow > 'old_student_ids' because the former would be an AliasEntry and the > latter a MultiAliasEntry. On the other hand the user directory tool > could happily allow a member of staff to find a student by typing in an > 'old_student_ids' value. The bit I'm not sure about is if MultAlias(Entry) will truely be able to be the parent class of Alias(Entry) due to the database design. > The main purpose of the MultiAlias would be for administrative data and > wouldn't necessarily contain aliases of interest to staff and student. > > The other advantage of subclassing is that we can implement it at Leeds > with Bodington 2.6 as an add-in without changing the existing code or > the definitions of the existing database tables. Hopefully. > Give that man a pay rise! :-) -- -- Matthew Buckett, VLE Developer -- Learning Technologies Group, Oxford University Computing Services -- Tel: +44 (0)1865 283660 http://www.oucs.ox.ac.uk/ltg/ |