Database assignment design.

[rgwfly]

Hello,
I have been struggling with a design approach for a new database.
Basically we manage 6 - 10 projects for a long duration. I may have 100 Technicians qualified to work on various projects. Base on their performance some may become unqualified for a particular project. I am trying to develop a table(s) where a supervisor can check what projects a technician can be assigned. I already developed most of the database to track their performance. but this was a recent change in scope. :banghead:
One though was to add the projects to the design table for each technician. However this is against good database design.
Any input would be appreciated.

 

[Grumm]

I would just make a table with the project information. Then in the table of the technicians i add a field project_ID. So that i can pick one or more technicians for 1 project.
That allows you to get projects that are not linked to any technicians and select the correct technician based on your already developed performance tracking.

(You probably need an extra table if the relation between project and technician is a many to many one)

 

[rgwfly]

Thanks for the quick reply. Since it is many to many I created a third table. Now I am just thinking of the best way to manage it. The end user (manager) would need to be able to maintain the appropriate authorizations on this base on performance.
So if I had all technicians assigned with all projects and a Boolean switch in the table.
If this is the best way to go.
tblTech:
TechNum
Lname
Fname

tblProject:
ProjectNum
ProjectName



tblQualified:
ProjNum
Technum
Qualified Y/N

 

[Grumm]

Can you tell us how you determine the performance of a technician ?
This will help if you need just a boolean or different table.
Is the performance based on previous projects ?

 

[rgwfly]

Their performance is determined by a pass or fail task rate on each project.
One project may have 5% tolerance. so if a technician fails 6 task out of 100 he becomes unqualified for that particular project. he may also become unqualified if he fails three in a row. But still may be qualified for other projects.
Most other projects will have a 20% or somewhere in between. This is also subjective based on the complexity of the test. this was done manually on spreadsheets before.

 

[rgwfly]

Let me back up just a bit. the technicians may be selected for new projects based on performance of previous jobs. Again subjective but the manager would like to be able to select this and then measure the performance which I already have done.

 

[The_Doc_Man]

The rules to follow in this case - and most other cases, actually - are quite simple.

Rule #1: If you can't do it on paper, you can't do it in Access. Meaning, you have to be able to enumerate the rules before you can hope to program anything. In a fluid environment, your conformance to that rule seems on-again, off-again.

Rule #2: Access won't tell you anything you didn't tell it first - whether your are telling it actual data or a method of computation. Meaning, sometimes you have to work backwards, cognizant of rule #1, and say "This is what I need out of it. How (mathematically or logically) do I do this? From where will I get the information needed to do this." So you need to know the contributors to your qualifications list because from context, you will have to compute it.

Don't forget to factor in whether it is possible to RE-qualify for a project.

Another factor in this is that it sounds like your criteria can't be determined quite as trivially as one might like. What concerns me for this facet of your question is that you say "If the person fails three times" or "If the person fails on x tasks out of 100." Unless the individual task skills can overlap a lot, it sounds like initially, EVERYONE would be qualified, but after folks start failing, then the question becomes complex over time. But what isn't clear in this discussion is how (or even IF) person X can be determined to be disqualified at the start of a project.

 

[jdraw]

I already developed most of the database to track their performance.

Perhaps it would be easier for readers if you could show us the tables and relationships for the existing database.

 

[rgwfly]

Thanks Doc for your perspective.
This project is definitely unique in its structure. I was always used to being able to quantify information and translating it to database design. I have been using Access for several years but also trying to take it to a new level by learning sql and more user friendly databases.
One thing I failed to mention is a new technician may become certified in being able to work various projects which a supervisor would assign. then the measure begins for that technician and he has to follow the rules above.

 

[The_Doc_Man]

Advice for user-friendly databases:

Clutter is ugly and contra-productive. With a switchboard or dispatcher form that launches other functions, it becomes easier for you to control the user experience and unify what they see into a consistent theme / style / approach. "Divide and conquer" works well in the case where you have some items that are base elements for which an individual maintenance form might be appropriate.

I had a database where I had five major tables and a pot-load of "translation" tables that defined names and secondary properties. I had separate forms to maintain each translation table, thus making them single-table forms, which any wizard can build for you - or at least, can get you started. My rule was that if it had a single-field PK that was or at least could have been autonumbered, it was a candidate for a single-table maintenance form. Things that involved junction tables? Totally different story.

This helps you de-clutter forms by separating out the background maintenance from the main thrust of the application. If you didn't think of that, just take a mental look at it. If you had already started that approach, then consider it corroboration of that choice.

Another approach I used to make things more user-friendly is that early in the project I created a TEMPLATE form that had stuff that would be common to all other forms - like a COMMIT (save) button and a CANCEL (undo) button and a HELP button and a FILE TROUBLE REPORT button and a CLOSE button, plus a lot of the supporting VBA code for those buttons and the common form events - Open, Load, Current, BeforeUpdate, Close, Unload, Activate, form-based OnError (to prevent Access being the handler), and whatever else was needed across many forms. Then, once you get the template right, copy it and start filling in the blanks.

I also had support modules so that the stuff in the class modules could call common routines like a unified trap manager. The form would catch the trap but then would immediately pass formal arguments to the handler subroutine so that (a) any trap would look professionally handled to the user and (b) I could assure proper logging of such events no matter which form encountered them. I also had things that you could call to identify the current user and, based on a user table, set some public variables that described what that user could do. Then the forms just had to call the user checker once at form open and thereafter, could use the global values without having to look them up at each function.

This is "user friendly" in the sense that if you use templates, you unify the "look-and-feel" of the various parts of the database that the user sees. If users can have different roles, then whatever they see STILL looks unified.

Again, consider it as another person's viewpoint on "user friendly" concepts. Users don't want to see totally different styles of operation for each form. They want to see forms that they feel are predictable so that THEY don't get any surprises.