in the users table there is a column named system_id . The database does not have a unique constraint on this column, however, the OpenMRS API enforces the uniqueness of this column. My question is, what was the main use case of the column when it was created? What purpose was it intended for?
Hey @willa! Good to hear from you.
The users.system_id in OpenMRS is meant to be a unique identifier for users of the system to be used in clinical documentation. The original implementation is a numerical counter with a check digit added to allow typographical errors to be caught when these numbers are transcribed.
I would guess the database column lacks a uniqueness constraint because cases arose where a user was retired and later “unretired” with the need to add new row with the same system id – i.e., the API enforcing uniqueness across unretired users.
1 Like
Couldn’t this use case be implemented using the users.username field since it is also required to be unique by the API (interestingly the database does not have a constraint to enforce uniqueness)? I guess what I am asking is why the need of both username & system_id?
The system_id is a system-generated identifier like 123-0, while the username is intended to be a user-friendly username for login that – depending on implementation-specific policies - can be managed by the admin (e.g., following a convention like first initial + surname) or even left to the user (e.g., willapower), so users aren’t forced to remember or login with their assigned system ID.
The lack of a database level constraint is the same reason as for system_id – i.e., the constraint is “unique across all rows where not retired.”
1 Like