I have been given the task of adding case worker information to our database. The database is SQL Server 7.
Here's the relationship breakdown as I understood it:
The case worker is employed by the agency.
The agency has many locations
The locations house many caseworkers.
[Attached is a jpg of a db diagram showing what I built.]
Now the problem:
I found out that the agency does indeed have many offices, but the caseworker can work at many offices.
A couple of examples would be:
Caseworker 1 may work Monday-Wednesday at office 1 and Thursday-Friday at office 2
Caseworker 2 may work mornings at office 1 and evenings at office 3.
My db structure ties him to one office.
The question is...
How would I layout the data structure to accommodate for those workers that work in many offices? My employer is big on data normalization and would like to use one ID to identify the caseworker, the location he/she is at, and the agency he/she works for.