role

Table Name: role

The role table is used to store information about different roles within the system, which are assigned to users to determine their access rights and responsibilities. These roles help to manage permissions and user actions.

---

Columns

Column Name	Data Type	Constraints	Description
roleId	smallint(6)	Primary Key, Not Null	The unique identifier for the role.
createdDate	datetime	Nullable	The date and time when the role was created.
description	varchar(255)	Nullable	A brief description of the role.
lastEditedDate	datetime	Nullable	The date and time when the role was last edited.
rolename	varchar(30)	Unique, Nullable	The name of the role (e.g., "Admin", "Manager", "User").
createdByUserId	int(11)	Nullable, Foreign Key	The user ID of the person who created this role.
lastEditedByUserId	int(11)	Nullable, Foreign Key	The user ID of the person who last edited this role.
synchronizable	tinyint(4)	Nullable	Indicates whether the role can be synchronized with external systems (e.g., 1 for true, 0 for false).
isVaccinator	tinyint(4)	Nullable	Indicates whether the role is assigned to a vaccinator (e.g., 1 for true, 0 for false).

---

Indexes

1. Primary Key:
   • roleId: Uniquely identifies each role in the system.
2. Unique Key:
   • rolename: Ensures that each role name is unique across all roles.
3. Foreign Keys:
   • role_createdByUserId_user_mappedId_FK:
     • References user(mappedId) for the createdByUserId column.
     • Links the role to the user who created it.
   • role_lastEditedByUserId_user_mappedId_FK:
     • References user(mappedId) for the lastEditedByUserId column.
     • Links the role to the user who last edited it.

---

Foreign Key Relations

1. role_createdByUserId_user_mappedId_FK:
   • Referenced Table: user
   • Referenced Column: mappedId
   • Description: Ensures that the createdByUserId exists as a valid mappedId in the user table, thus maintaining a relationship with the user who created the role.
2. role_lastEditedByUserId_user_mappedId_FK:
   • Referenced Table: user
   • Referenced Column: mappedId
   • Description: Ensures that the lastEditedByUserId exists as a valid mappedId in the user table, maintaining a relationship with the user who last edited the role.

---

Usage Notes

• Purpose:
  • The role table is used to define various roles within the system. Each role can have specific permissions, and users are assigned these roles to manage access control.
• Key Attributes:
  • roleId: A unique identifier for each role.
  • rolename: The unique name associated with the role.
  • createdByUserId: The user who created this role.
  • lastEditedByUserId: The user who last edited this role.
  • synchronizable: Whether the role is synchronizable.
  • isVaccinator: Indicates if the role is associated with vaccinators.