Hi
I have been asked to design and build a database to track maintenance and i have a few questions:
Scenario:
There are multiple things that need to be tracked/recorded.
TBL_Maintenance
-MaintenanceTaskNumber
-WorkCity
-WorkSite
-DueDate
-DescriptionOfTask
-ActionRequired
-AccessStatus
-EngineerStatus
-InspectorStatus
-OverallStatus (Unplanned, Access Booked,Access pending, Engineer Booked,Inspector Booked, Cancelled)
-UpdatedDate
An access booking must be made
TBL_Access
-MaintenanceTaskNumber
-AccessNumber
-AccessStatus (Approved,Pending,Not required)
-AccessStartDate
-AccessEndDate
An enginner needs to be assigned
TBL_Engineer
-MaintenanceTaskNumber
-EngineerName
-EngineerBookedDate (needs to be between AccessStart and AccessEnd)
-EngineerPhoneNumber
A supervisor/Inspector but be booked
TBL_Inspector
-MaintenanceTaskNumber
-InspectorName
-InspectorBookedDate (needs to be between AccessStart and AccessEnd)
actions and comments needed to be captured for reporting
TBL_Action
-MaintenanceTaskNumber
-Changetype ( Access Booked, Engineer Booked, Inspector Booked,Cancelled)
-Comments
-CancelledReasonCode
-ChangedBy
-ChangeDate
The database will have rolling data sent from an external Database as an excel file ( Once the work is completed it will re-output the new date to be done.)
on receiving the excel file it will need to check if the record already exists or create a new record
I was thinking that the (MaintenanceTaskNumber+DueDate) should be the unique key.
There needs to be a userforms from end that allows the planner record the bookings.
Some of the reports required are to show any unplanned, any overdue, Current state of planning, Time Taken to plan(each step)
Would someone kindly helping me check i have the correct logic and foundations of the database set correctly?
I have been asked to design and build a database to track maintenance and i have a few questions:
Scenario:
There are multiple things that need to be tracked/recorded.
TBL_Maintenance
-MaintenanceTaskNumber
-WorkCity
-WorkSite
-DueDate
-DescriptionOfTask
-ActionRequired
-AccessStatus
-EngineerStatus
-InspectorStatus
-OverallStatus (Unplanned, Access Booked,Access pending, Engineer Booked,Inspector Booked, Cancelled)
-UpdatedDate
An access booking must be made
TBL_Access
-MaintenanceTaskNumber
-AccessNumber
-AccessStatus (Approved,Pending,Not required)
-AccessStartDate
-AccessEndDate
An enginner needs to be assigned
TBL_Engineer
-MaintenanceTaskNumber
-EngineerName
-EngineerBookedDate (needs to be between AccessStart and AccessEnd)
-EngineerPhoneNumber
A supervisor/Inspector but be booked
TBL_Inspector
-MaintenanceTaskNumber
-InspectorName
-InspectorBookedDate (needs to be between AccessStart and AccessEnd)
actions and comments needed to be captured for reporting
TBL_Action
-MaintenanceTaskNumber
-Changetype ( Access Booked, Engineer Booked, Inspector Booked,Cancelled)
-Comments
-CancelledReasonCode
-ChangedBy
-ChangeDate
The database will have rolling data sent from an external Database as an excel file ( Once the work is completed it will re-output the new date to be done.)
on receiving the excel file it will need to check if the record already exists or create a new record
I was thinking that the (MaintenanceTaskNumber+DueDate) should be the unique key.
There needs to be a userforms from end that allows the planner record the bookings.
Some of the reports required are to show any unplanned, any overdue, Current state of planning, Time Taken to plan(each step)
Would someone kindly helping me check i have the correct logic and foundations of the database set correctly?
Last edited: