Theory on how to design the tables in this project

Posted on 2005-05-10
Last Modified: 2010-03-19
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.
Question by:JF0
    LVL 17

    Expert Comment

    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
    LVL 18

    Accepted Solution

    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...

    hospital_id int identity
    hospital_name  varchar(50)

    1  General Hospital
    2  Community Hospital

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

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

    Then, you'd have your hospital_shift table

    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


    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now