permission

Table Name: permission

The permission table holds information about the different permissions that can be assigned to users or roles within the system. Permissions control access to various features and actions.

---

Columns

Column Name	Data Type	Constraints	Description
permissionId	smallint(6)	Primary Key, Not Null	The unique identifier for the permission.
createdDate	datetime	Nullable	The date and time when the permission was created.
description	varchar(255)	Nullable	A brief description of the permission.
lastEditedDate	datetime	Nullable	The date and time when the permission was last edited.
permissionname	varchar(100)	Unique, Nullable	The name of the permission (e.g., "View Reports", "Manage Users").
createdByUserId	int(11)	Nullable, Foreign Key	The user ID of the person who created this permission.
lastEditedByUserId	int(11)	Nullable, Foreign Key	The user ID of the person who last edited this permission.

---

Indexes

1. Primary Key:
   • permissionId: Uniquely identifies each permission in the system.
2. Unique Key:
   • permissionname: Ensures that each permission name is unique across all permissions.
3. Foreign Keys:
   • permission_createdByUserId_user_mappedId_FK:
     • References user(mappedId) for the createdByUserId column.
     • Links the permission to the user who created it.
   • permission_lastEditedByUserId_user_mappedId_FK:
     • References user(mappedId) for the lastEditedByUserId column.
     • Links the permission to the user who last edited it.

---

Foreign Key Relations

1. permission_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 permission.
2. permission_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 permission.

---

Usage Notes

• Purpose:
  • The permission table is used to define various permissions that control access to features within the system. These permissions can then be assigned to users or roles to determine what actions they are allowed to perform.
• Key Attributes:
  • permissionId: A unique identifier for each permission.
  • permissionname: The unique name associated with the permission.
  • createdByUserId: The user who created this permission.
  • lastEditedByUserId: The user who last edited this permission.