Theory on how to design the tables in this project
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Effectively, your data model should consist of entities (which become tables) and relationships between them. The entities in the most simplisitc case are the NOUNS in your descrption of the system, so you will have HOSPITALS, AGENCIES, NURSES, perhaps you will have SHIFTS. Your problem description does not have any other nouns, so there are no other major entities to model. In practice, a few extra tables may fall out if you find you have a many-to-many relationship (e.g. the relationship between AGENCIES and NURSES) which will need a join table ro resolve. Or if you need to do some further normalisation etc. However I'd be surprised if you get to 15 tables.
Start off with a blank sheet of paper. Draw all the main entities on it. Then think about all the relationships between them - are they 1-1, 1-many, many-many? draw these on your sheet of paper, and you have your basic conceptual model. Any many-many relationships will need a join table to resolve, otherwise you probably have all your tables at this stage.
"I was thinking about using a Join command if needed but rather not."
I think you need to pay some more attention to your professor. The whole concept of relational databases revolves around joining (relating) tables together. It is not something you should be trying to avoid, but rather something you should be embracing whole-heartedly. The only possible way to get the desired result is with queries which join your various entities together