# “Workers and tasks” need advice for table structure and relations in DB

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

1. Table T_workers contains personal record fields name, town... and sub-key ID_task referencing a task in table T_tasks.
2. 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.
3. Table Tlist_branches, a predefined list containing Nb branches from which to select a task branch.
4. 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 :

1. Create, display and delete worker record, modify worker personal static fields like name, town… as usual, no problem.

2. 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 ?

3. 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.

4. A given task can apply for many worker's. It tells that these workers are involved in the same task (same branch and role).

5. 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.

6. On a worker's data form, display the list of tasks a worker is involved in.

7. 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.

edit retag close merge delete

Sort by » oldest newest most voted

As mentioned in a previous response to you, there is some documentation (click here) which has a section titled "Planning & Designing your Database" (older version but basics still apply).

One of the items mentioned in your question was multiple tasks for workers. If you remove ID_task from T_workers table and add ID_worker to the T_tasks table, this should solve much of your problems. Then the T_tasks table brings the other three tables together and solves your multiple tasks problem.

You may also want to view this answer (click here) and view the sample to see how to display some of the things you are looking for.

A lot of what you ask is covered in various sections of the documentation. Please look it over.

more