I just accepted a job for a moderately sized state agency and I've come in to find they live in the middle ages. I work in the public hotline, where people call in and our job is to connect them to the people that are experts in dealing with the particular subject they need, in the geographical area they live in. Currently that is done by all of the other operators having done this job for a decade plus and having piles of random staff lists printed out and pinned to the walls of their cubes. Half the time those lists are out of date and many of the programs change names frequently enough that I'm having nightmares about my co-workers leaving and no one knowing who to send calls too.
So, to that end, I'm trying to sit down and create a database that will track this and ease everyone's life substantially. We don't have any money budgeted for an actual developer or any real tools, so I'm starting in Access. So here is the question:
I need to provide a tool that can tie together a list of over 1000 staff members spread throughout the state, associate each one with an arbitrary number of "services" or "programs" (some people only handle one thing, some people do a dozen different things) for an arbitrary number of counties (some services are handled statewide by a single person, others county-by-county, and some in larger regional groups).
SO what I have right now is a single staff table, with all of the relevant info (name, division, contact info, etc), and another "services" table that is a list of all the different programs that people might call in for help with, I also have a table with an entry for every county in the state. So What I'm thinking is I need to put a weak entity table in between tblStaff and and tblServices that I'm tentatively calling tblZones, and that table represents a staff member providing a certain service for a period of time.
Where I'm getting tripped up is on whether or not I can also use the tblZone to act as a sort of tripartite weak entity to show Staff>Service>County, or if I should have another weak entity between tblZone and tblCounty?
So right now tblZone has a PK, FK's to both tblStaff and tblServices, and I made a separate tblZoneCountyLink table that has a PK, and FK's to tblZone and tblCounty. I think this will work, but I'm not sure if that is too unwieldy to make a form that can create viable record.
Any insight or suggestions would be greatly appreciated. Thanks.