Quite a Challenge

stebut05

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

Roger_Ramjet

Ah! A crash course in data analysis and data normalisation, and then in database design.

Look, this project as a database is bigger than you think. On the other hand, the benefits that your organisation is going to get from it are also bigger than you think. So your bets bet is to invest a little time in learning what it is all about. There are several great articles here on PHPBuilder to get you started. But I'd say go and buy a book on the subject, cos the skills you can acquire while you are at it will last the rest of your career: and that applies even if your career is not in IT.

Not to disapoint you too much though, here are a few pointers:
you need to identify the 'entities' involved:
1. each machine is an entity, cos you can go up and kick it, so you are going to need a table with an entry for each machine
2. the type of machine, and the maintenace plan that flows from, it is a seperate entity
2. the usage if each machine is a seperate entity, so you are going to need a usage table
3. the maintenance done on each machine is also a seperate entity
4. the activities involved in maintaining it are seperate enitities, cos an individual maint activity will be common to several maintenace plans: like an oil or filter change on your car's major and minor service cycles.
5. the people doing the maint are also seperate entities, cos you can go up and kick them as well.
6. their roles are also seperate entities, cos supervisors and engineers do different things, obviously, but the same role can be done by different people.
7. tha capacity of the engineering dept is a seperate entity, but composed of the number, roles, and skills of the people involved. This is a grey one that is going to require the application of 'Domain Knowledge' to decide how you calculate and store it
8. the maint schedule then derives from the machine type, the number of machines of each type, their required maint cycles, the specific activites in each maintenance stage in the maint cycle - especially the resources/parts, time and skills each such acitivity requires, the skills required for said activities, the people with those skills

Now, I don't know if whoever dumped this on you realises just how much is involve - but I'll bet they don't. Neither will they have fully realised just what an opportunity this also is - for the whole dept. The first real problem you face will be getting other people to spend the time this needs to define all the things I've listed above; though looking at your card image a lot of the information may already be available on those. In the process, you can apply some TQM (total quality management) to see how the whole process can be improved. You see, most people really have no idea just how much the automation of a clerical system by computer changes that system. But that is the opportunity, you can improve the whole process while you are at it. Now the process is going to change, as I said, so you can either harness that change for the good, or ignore it and that is usuall y for the bad.

Hope this does not put you off to much, and have fun while you are at it.