Quite Challenging!!!

Hi people,

Not sure if this should be posted here, but i'll give it a try.

I am looking for some advice regarding the development of a database, as I am no expert, but I’ve been asked to develop one. Hopefully, it will be a bit of challenge for someone out there, it has been for me!!! I have developed it, but would really like some advice, to see if I have developed it correctly, or if it could be improved.

Background
A database is required to record and monitor maintenance on a machine. This machine needs to maintain on a regular basis, and has different types of maintenance checks. A full maintenance (every 500 hrs) and half maintenance (200 hrs), different components get checked on each maintenance check, i.e. check component, grease component, change component etc.

At present this is a paper based system illustrated below.


Index card

Index Card Image



Each maintenance card is recorded on the index card to check that it has been completed and signed off


Work card

Work Card Image

As you can see the card title (clean component) is linked to the index card. I need to record each maintenance card in the database and all the tasks within the card, some of the tasks need to be signed of by maintenance person or engineer and this needs to replicated in the database, so that if any problems occur details of who inspected or maintained the machine can be recalled. This is where i'm having problems. I could just do one database record for each miantenace card, but some of the tasks have sign off boxes and others dont. This is an important apect of this system, so i cant add a sign off box to every task (if not needed). There are also notes and warning that need to be added as well, furthermore, some task can have sub tasks, or even sub-sub tasks. Finally, the new system cards. must have the same layout as the paper based version. This all leads to a headache!!!!!!!!!

My effort

I have tried to tackle this system as follows:

Have an index table that links to a maintenance type table, that then liks to a machine table. Then i have a maintenance description table:

Description_id (PK)
Detail_id (FK)
Page_number
Task_number
Sub_task
Sub_task_two
Maintenacne_description
Description_notes
Description_warning
Inspector_sign_off
Maintenance_sing_off


As you can see, its a mess i think. each task is now a record, but alot of the fields are NULL as not all are filled. ie. as illustrated above some of the tasks dont have notes, warning or sign offs.


I would really appreciate as much help and advice as possible and thanks in advance.


Regards,


Steven

Here's my 2 cents worth:

I'd be more worried about how to generate the reports as understanding the logic in report generation will assist the datafile creation. For example 500 hours from when ( machine up time or 500 hours straight )? If its only machine up time who decides to check the cards and who logs the hours to start with.

Presuming a 500 hour time limit I'd look at

MachineID -> ID, description, history, notes
Date
EventID -> ID, Event type ( ie new, sold, maintained, examA, examB etc )
SignOffID -> ID, PersonCode, Date, SignOffType ( Inspector, Fitter, etc )

A sub task is just a smaller event, so a lube event could be 200 and lube topup could be 210.

I'm looking at a broader aspect of machine maintenance where this program could be used for any machine and events not described can be added as new machinery comes on line

This method runs one record per event. A reporting system then starts at the report date and polls all records backwards to a nominated date/event. The report method with rules would then be looking for maintenance being done, not signed off, not inspected, etc.

Hope this gives you some ideas

pjv