Best practice forum (Archived)

Usage of 'idnumber' field - are values unique?

Hi,

In the upcoming 2.5 release we are considering adding a requirement to force the 'idnumber' field to be unique across the site (as used in quite a few places such as users, organisations, positions, competencies etc.

The reason for this is that the idnumber is used as an indentifying key in Totara sync so having more than one record with the same idnumber can cause problems.

Before we do it I just wanted to gather feedback from the community to see if anyone was using the 'idnumber' field in an unexpected way and if making the field unique would be a problem for anyone.

Thanks,

Simon

Permalink | Reply

I am certainly aware that on our system there are a number of users whose ID number is blank or not unique, how will the upgrade to 2.5 handle this? We've definitely had problems with this where a new user has been created rather than a deleted one revived so you end up with two accounts with the same ID for example.

I think enforcing ID number as unique and not null is a good idea, as you say it's used across a wide range of applications. My only query would be that you said they should be unique "across the site", but we (by design) use the same ID numbers for users and their positions, do you just mean that ID number in the user table needs to be unique?

Permalink | Show parent | Reply

We would continue to allow blank values for ID Number since that would be an invalid sync value anyway. We would check for duplicates at the start of the 2.5 upgrade and prevent upgrade until they were fixed.

In terms of how we measure uniqueness - we would check each type of object that uses idnumber is unique - so you couldn't have two users with the same idnumber but you could still have a position and a user with the same idnumber.

Simon

Permalink | Show parent | Reply

Hi Simon,

I would really like this field to be unique as it would prevent duplicate accounts being created in our organisation.

That said, what happens if you change it in the upgrade and their are existing duplicate id's in the system?

Regards,

Russell

Permalink | Show parent | Reply

Hi Russell,

We would probably include a check early in the 2.5 upgrade process, list any duplicates found and prevent upgrading until the issues were fixed.

Simon

Permalink | Show parent | Reply

Just to clarify what we are talking about here. The system allocates a unique identifier called the User ID. The User ID Number can currently be updated by the user when they edit their profile. Why do we need two unique identifiers? If the User ID number is to be unique then it needs to be removed from the fields able to be ameneded by the user.

Permalink | Show parent | Reply

Hi Suzanne,

The internal User ID is the number used by Totara in the database. It is assigned by the system and can't be changed. The ID Number however can be any string and it doesn't have to be numbers. Typically people will put an "Employee ID number" or some other unique identifier that means something to the organisation. Because it has meaning outside of Totara it is often the ideal key to use to sync data which is why we use it in Totara sync.

I agree that having this field editable doesn't really make a lot of sense in this context - we should probably add a new capability (disabled by default) that locks the idnumber field if the user doesn't have permission to change it.

Simon

Permalink | Show parent | Reply

Would definitely like that capability, it does seem strange to have a field like ID Number editable by users. Would it be possible to extend that to other fields (maybe being able to identify fields as "user editable") and tie that in with the capability?

Dan

Permalink | Show parent | Reply

It's a good idea, but unfortunately we're pretty busy with the 2.5 release at the moment so I don't think we'll have time to extend this feature further at this time.

Simon

Permalink | Show parent | Reply

That's fine, it's a longer time Nice to have rather than an immediate thing I think, feel free just to add it to the enhancement requests for 2.6.

Dan

Permalink | Show parent | Reply

This can't come soon enough. Most people expect an "idnumber" to be unique and this has led to a lot of confusion.

I've long assumed that there must be a really good reason for duplicates being allowed, possibly to fit with external systems— though I believe any extra work necessary to get data unique in Totara and fitwith other systems must be worth the effort for anybody.

Permalink | Show parent | Reply

It's good to see so much enthusiasm for this - I had expected at least one response like this one:

https://tracker.moodle.org/browse/MDL-14084?focusedCommentId=54057&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-54057

We do try to be careful about backward compatibility and we don't want to break the system for people who are already using it a certain way.

Therefore our likely strategy will be to implement checks within the UI for 2.5 (but not enforce uniqueness at the database level). Assuming we don't have any problems we will then add a unique database index in a later release.

If we do get complaints we may need to add a configuration setting and forego the database index entirely.

Simon

Permalink | Show parent | Reply

Also, in terms of which objects will be affected, our intention is to add this check for all Totara tables, but none of the Moodle tables except the 'user' table (which we need for sync).

Included tables:

"mdl_comp"
"mdl_comp_framework"
"mdl_comp_scale_values"
"mdl_comp_type"
"mdl_dp_objective_scale_value"
"mdl_dp_priority_scale_value"
"mdl_goal"
"mdl_goal_framework"
"mdl_goal_scale_values"
"mdl_goal_type"
"mdl_org"
"mdl_org_framework"
"mdl_org_type"
"mdl_pos"
"mdl_pos_assignment"
"mdl_pos_assignment_history"
"mdl_pos_framework"
"mdl_pos_type"
"mdl_prog"
"mdl_user"

Excluded tables:

"mdl_cohort"
"mdl_course"
"mdl_course_categories"
"mdl_course_modules"
"mdl_grade_items"
"mdl_grade_items_history"
"mdl_groupings"
"mdl_groups"
"mdl_mnetservice_enrol_courses"

Simon

Permalink | Show parent | Reply