Hello everybody
“Workers and tasks” unsolved example
or "what would be the best table structure and relations to implement such DB ?"
I have to set-up what looks like a quite simple DB, but after a few trials, I am unable implement successfully the table relations and user forms, I mean without data duplication, ! Here is the challenge :
Four tables
- Table T_workers contains personal record fields name, town… and sub-key ID_task referencing a task in table T_tasks.
- Table T_tasks (auxiliary table, empty at start) with two sub-key fields ID_branch and ID_role (a task is here defined as an association of a branch selection, and/or a role selection from the tables below.
- Table Tlist_branches, a predefined list containing Nb branches from which to select a task branch.
- Table Tlist_roles, a predefined list containing Nr roles from which to select a task role.
Forms
Functionality has to satisfy the following needs and rules :
-
Create, display and delete worker record, modify worker personal static fields like name, town… as usual, no problem.
-
Display, modify, add new, delete tasks. Is the idea of an auxiliary table T_tasks a good or bad approach when considering the next points ?
-
A worker can be involved in one or many different tasks, limited only by the maximum different combinations of Nb branches with Nr roles from the predefined lists.
-
A given task can apply for many worker’s. It tells that these workers are involved in the same task (same branch and role).
-
So the number of records in T_tasks increases or decreases dynamically according to the tasks added or deleted for different workers when updating data.
-
On a worker’s data form, display the list of tasks a worker is involved in.
-
Of course one has also to propose the search/selection of a particular worker from a workers list_box.
So I am more then lost in defining the overall structure !
Queries
The queries have to filter the workers on branch or role criteria or both from T_tasks, as well as “ordinary” easy filters on town and other simple static field criteria from T_workers.
Note :
With branches and roles duplication in T_workers records AND a limited and predetermined number of allowed task-fields (I used five), I can cope with the problem in a redundant and non elegant way.
In that case modifying a record in the branch or role list, will not update the duplicated data, and maintenance becomes tricky ;-(
If anybody knows a good approach (if not the best ?) to implement such a data base, I would be grateful to benefit from useful advice or example.
DB file, with the four tables described above as a start and a form skeleton download here : http://r.kirsch.free.fr/BD_LibO/WorkersAndTasksV0.odb
Robert.