Theory on how to design the tables in this project
Posted on 2005-05-10
I am in an intro to DB class, and we are starting to learn SLQ server. Our professor gave us a few weeks to come up with (and make work) the following project:
Project will consist of 4 hospitals, 5 staffing agencies, and 10 nurses. The 4 hospitals will post what shifts they need filled. The nurses will post what shifts they are available. The agencies will be assigned to certain nurses (some will have multiple nurses and some nurses will belong to multiple agencies).
We will run queries as a hospital to find matches for their open shifts to the nurses available shifts, however the nurses MUST go through the agencies.
We think we have come up with a way to do it, but it involves around 15 tables... This is all theory at this point, but eventually we will code it.
The column names we will need are as followed:
Hospital name, Hospital location, Hospital shifts needed, Agency name, Agency location, Nurse name, and Nurse shift availability.
Any ideas on how you would set these up so that they can "link up" and run a query successfully? I was thinking about using a Join command if needed but rather not.