I am putting together a database of our employees and the areas in the hospital they are responsible for. In this I am including these fields

 

1. Department

2. Technician

3. Backup Technician

4. Extension

5. Pager

6. Cell Phone Number

 

There is going to be many departments per tech but only the same ext, pager & cell phone# each of them. Should this be just a flat file database or should it be setup with relationships of each techs departments to them? I am not sure how to relate each technician to multiple departments.

I believe you should start with a table of Technicians and a table of Departments. The rest depends: you say that one technician can have many departments; if the reverse - one department can have many technicians - is also true, then you should have a third table (a so-called join table) to record individual assignments of a technician to a department. Otherwise you only need to add a TechnicianID field to the Departments table and populate it with the unique ID of the technician assigned to that department.

Note that Filemaker allows you to assign a technician to many departments using a checkbox field in the Technicians table and manage without a join table. However, this will not allow you to record any specific details about a join - e.g. the date of assignment or the role (e.g. being a main technician or a backup one) -  and also reduce your reporting ability - e.g. producing a report of technicians by department.

 

Edited August 10, 20196 yr by comment

Ok! I understand what your saying. I though I may have to assign a number to do the joins. This may end up being a growing database with more features later on. I will setup the tables and joins like you suggest. I appreciate you replying back.

 

Kevin