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.
LVL 16
JF0Asked:
Who is Participating?
 
mdouganCommented:
Hi JF0,

The methodology that I use is to first ask what are the main "objects" we're working with... in your case, I think you've named them... hospitals, agencies, nurses, shifts.  Each of these will have a unique key... usually just an auto-incrementing number.  Then, there are tables that associate one table with another one... sometimes these are called join tables.  For example, you'd probably want a hospital_shift table, a nurse_shift table, maybe a nurse_agency table.  These association tables usually just have two or three columns... maybe the primary key from each of the two tables (hospital_id and shift_id; nurse_id and shift_id etc.)  The primary key of this table might be both of those columns together.  Or, sometimes, if it's OK to have duplicate entries for both of those numbers, you'll give the table it's own auto-incrementing column for a primary key (all of your tables should have primary keys)

Once you've figured out what to put in each of the tables, then you'll use queries to select data out of the tables for whatever purpose you need... these will almost always require you to join one or more tables.  

So, just a simple example...

Hospitals
hospital_id int identity
hospital_name  varchar(50)

sample
1  General Hospital
2  Community Hospital

Shifts
shift_id int identity
shift_name   varchar(50)
shift_start_time datetime
shift_end_time   datetime

sample
1  Graveyard  12:00 AM    08:00 AM
2  Morning    08:00 AM    04:00 PM

Then, you'd have your hospital_shift table
hospital_id
shift_id

sample
1 2
2 1
2 2

If you want to list all the shifts by hospital, your query would look like:

select hospital_name, shift_name
from hospitals
join hospital_shift
on hospital_shift.hospital_id = hospitals.hospital_id
join shifts
  on hospital_Shift.shift_id = shifts.shift_id
order by hospital_name, shift_name

and your results would be:

Community Hospital  Graveyard
Community Hospital  Morning
General Hospital    Morning

Cheers!
0
 
BillAn1Commented:
I can't imagine a realistic model that needs more than 5 or 6 tables, so can you give some idea of the 15 you are proposing?
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.