2006-09-14 Developers Conference Call

Consider giving role and privilege tables an autonumbered primary key as integer. We would need to constrain role and privilege names to be unique & indexed, so we could still refer to roles and privileges by name within code, but inside the API use something like SELECT FROM role WHERE name = ?.

From: Mike McKay
Sent: Wed 9/13/2006 11:09 AM
To: Darius Jazayeri
Subject: OpenMRS users section

Hi Darius,

I hope things are going well in Rwanda and the rest of the PIH universe.
We have just begun an OpenMRS rewrite of our ART system, and have a
question for you.

Almost every table in OpenMRS has an auto-incrementing primary key (or
composite keys - which rails now supports).

But two tables in the user section are different: role and privilege.
Instead of auto-incrementing primary keys, there are VARCHARs that are
used as primary keys and foreign keys. I think I brought this up when we
were in Boston, but I don't remember the resolution.

Besides the inefficiencies of using VARCHARs as foreign keys, it is odd
that it breaks consistency with the rest of the schema. Ideally, we
would like to see the role and privilege table altered with the
following fields added:

role_id: INTEGER as the primary key for role
privilege_id: INTEGER as the primary key for privelege

Then the join tables: role_role, user_role, and role_privilege would all
use these as the foreign key instead of the VARCHAR values.

For us, it is important because rails appears to require primary keys
that are integers. We really don't want to alter the OpenMRS schema, and
we can probably find a rails workaround so that we can stay on the same
schema, but maybe this change would benefit everyone involved.

Let me know your thoughts.

Cheers,

Mike