compliancemanagement

Table: compliancemanagement

The compliancemanagement table stores records of compliance checks at various locations.
It tracks the status, observations, and audit information for compliance monitoring and reporting.

---

Columns

Column Name	Data Type	Constraints	Description
complianceManagementId	int(11)	NOT NULL, AUTO_INCREMENT	Unique identifier for each compliance record
complianceDate	datetime	NULL	Date of the compliance check
createdDate	datetime	NULL	Timestamp when the record was created
description	varchar(50)	NULL	Short description of the compliance activity
lastEditedDate	datetime	NULL	Timestamp when the record was last modified
locationId	int(11)	NULL	References the location where compliance was checked
mappedId	int(11)	NOT NULL	Reference ID from the id mapping system
status	varchar(20)	NULL	Status of compliance (e.g., compliant, non-compliant)
voided	tinyint(1)	NULL	Soft deletion flag (1 = voided, 0 = active)
voidedDate	datetime	NULL	Date when the record was voided
voidedReason	varchar(50)	NULL	Reason for voiding the record
createdByUserId	int(11)	NULL	User who created the record
lastEditedByUserId	int(11)	NULL	User who last edited the record

---

Indexes

1. PRIMARY - Primary key on complianceManagementId
2. complianceManagement_locationId_location_locationId_FK - Index on locationId
3. mappedId_compliance_FK - Index on mappedId
4. complianceManagement_lastEditedByUserId_user_mappedId_FK - Index on lastEditedByUserId
5. complianceManagement_createdByUserId_user_mappedId_FK - Index on createdByUserId

---

Foreign Key Relations

1. complianceManagement_createdByUserId_user_mappedId_FK

   • createdByUserId → user.mappedId
   • Tracks who created the record

2. complianceManagement_lastEditedByUserId_user_mappedId_FK

   • lastEditedByUserId → user.mappedId
   • Tracks who last edited the record

3. complianceManagement_locationId_location_locationId_FK

   • locationId → location.locationId
   • Associates the record with a specific location

4. mappedId_compliance_FK

   • mappedId → idmapper.mappedId
   • Reference to ID mapping system

---

Usage Notes

• Tracks compliance activities for monitoring and reporting purposes.
• status indicates whether compliance requirements were met.
• Soft deletion is handled via voided, voidedDate, and voidedReason.
• Audit fields (createdByUserId, lastEditedByUserId, createdDate, lastEditedDate) maintain history.
• Ensures referential integrity with user, location, and idmapper tables.