[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Database structure; working like a flowchart?

Posted on 2009-02-24
Medium Priority
Last Modified: 2012-05-06

I'm creating a database of medical professionals and patients.  Obviously the information for each of these person types varies quite a lot.  For the professionals I need to record their occupation and place of work, while for the patients I also need their occupation but that is much more varied than the possible occupations of those working in medicine, and also need to record their conditions and medications.

I have one central table (Table 1) upon which I think this will all rest.  Table 1 records all personal info like name, contact details, home address, etc.  Is there a way to indicate in this table that if a field is filled with "professional" it will take you to the various tables which need to be filled for that professional ie Type of Doctor, Department in the relevant tables .  And if you are a "patient" then you are directed to the tables on medical conditions and medications.

Is this possible?  I get confused between triggers, procedures, and views, and I'm starting to think that this structure may not be the best way as I am envisaging it working like a flowchart.

Many thanks
Question by:gd1982
LVL 43

Expert Comment

ID: 23721974
This must be realized by application logic. Don't forget a possibility the professional doctor can be a patient also. So the column which will recognize between doctors and patients is probably not the right one. Instead of this recongnition you should simply create views from your central Table1 joined with child tables containing information specific for doctors at one side and information specific for patients on other side. Each view will contain right information depending on fact if such information exists...

You should think about your data model again. Is it correct to use Table1 contatining personal info as the "central table"? I would rather say Table1 contains personal info and it is used everywhere you need personal info. Two other tables should be used for patients and doctors as their central storage and these two tables could have their own relations depending on your needs.

Author Comment

ID: 23722153
Basically to summarise, I have Table 1 which captures all the info for patients and professionals which they have, such as "name surname, dob, age, telephone, home address" etc.  This information is needed for everyone regardless of patient or professional status.

This is where the similarity ends however and from then on I need to capture different info for each person type.  That is why I referred to table 1 as "central" as it is the only commonground shared.

I had envisaged the database structure would, at this point on, divide into two halves, one 'side' for patients and all their related info, and the other 'side' for professionals.

Does that make it any clearer?  Is a view the best way to achieve my goal or is the structure of the database a fundemental problem?

This is my first database so a lot of trial and error.
PS.  Patients with a professional medical background are excluded from joining the database.
LVL 70

Expert Comment

by:Scott Pletcher
ID: 23723637
First, you need to review the data requirements with someone who can help you accurately answer questions like:

1) When a patient name changes, do we need to be able to look up the old name and the new name?

2) When a patient address changes, do we need to "know" about the old address and/or lookup on it? [For example, for prior insurance claims filed under the prior address.]

3) How many different tele # could be stored for one patient? It's likely to be more than one -- business, home, cell -- so you would need a separate table for those.

4) etc....

Then, you need to find someone who can help you take those requirements and design the table*s* you will need to store "table1" information and the other info.
Trying to use one "main" table for everything is going to become a nightmare in the (not distant) future.
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.


Author Comment

ID: 23731876
Sorry guys I don't think I explained the problem particularly well.  So I'm going to have another go at it.

I am creating a database which will store the information about medical professionals and medical patients.  Below is a list of tables.  

The table in the first row (presented below) is the common area that all people on the database will belong to and is necessary information.  The second row is all the tables which gather information on the Medical Professionals.  And the third row is of the tables which pertain to the medical patients.

What I need to know is if this structure is flawed as I am trying to collect info on two very different types of people within one database.  For my purpose this is what I need.   But I am wondering if, with your expertise, you can see a better way, and if not, can you explain how I can set it up so that before you input someone, you choose whether they are a patient or professional and see only the relevant fields?

Many thanks.

1) tblRespondents                
    PK RespondentID            
    Address (etc)

2) tblProfOccupation               tblMedicalOccupations            tblGradeType
    PK ProfOccupationID          PK MedicalOccupationID         PK Gradetype      
    FK RespondentID                MedicalOccupationName        FK MedicalOccupationID
    FK MedicalOccupationID                                                    GradeTypeName
    FK GradeTypeID

3) tblMedicalConditions           tblMedications                tblPatientOccupation            
    PK MedicalConditionID         PK MedicationID             PK PatientOccuapationID
    FK RespondentID                FK RespondentID           FK RespondentID
    MedicalConditionName        MedicationName            PatientOccupationName
LVL 51

Expert Comment

by:Mark Wills
ID: 23746110
It does look quite good.

If we look at say, tblPatientOccupation  versus  tblProfOccupation - it might also seem resonable to have these as one table - assuming things like lookups on occupations were handled the same way and then occupation could become part of the respondents table.  Then medications could hang off the respondant ID as you have it now, and two look up tables being occupations and grades. Maybe occupations could be seggregated into industry groups such as medical, business etc to help narrow down some choices. Similarly, there could be parrallels in area of specialty versuse medical conditions - both professional and patients will tend to have "interests" in those areas and might be able to treat it in a similar manner as occupation (with appropriate look ups of categories).

The key will be how you arrange your screen to ask or capture the relevant pieces of information. If you want to create a pop-up for additional information, or a branch to a relevant screen for capturing the next layer of information, then it possibly does not make a great deal of difference. But if you would prefer to have all the required information on one screen, then it would make more sense to try to consolidate a little more, and maybe use "type of respondent" to help filter the available choices (means a change to the lookup tables).

Author Comment

ID: 23756212
Hi Mark,

Thanks for your observations, I had considered combining the Patient and Professional Occupations but being new to MSSQL I didn't want to overestimate my novice abilities.

Once all of this is completed I will be creating a web based front end for it (dont ask me how, yet).  My theory on how this will all work eventually is that from the front end a used will choose either patient or professional which will bring up a screen with the relevant fields for that respondent type.  

Can this also be done in the backend to fix to the front end app.  (I tried creating a view the other day, thinking that just as you can create one to query multiple tables I could also create one to input into several tables, ie all the patient relevant tables or professional relevant tabled.)

That's essentially what I am wondering.
LVL 51

Accepted Solution

Mark Wills earned 2000 total points
ID: 23771440
Well, would imagine some kind of registration and that should be pretty simple - like a sign up for either patient or professional and there favourite user name and password. Once you have that basic profile then you can display the next screen with the appropriate field labels in place, and possibly, the additional criteria needed to complete either profile.

The backend database can be a bit more "global" in approach, the front end will "know" that it is either patient or professional because of their profile, so, if needed can supply that piece of the puzzle to the backend. Meaning your database can be made "simpler" with a view (not sql view) to get information out the best / easiest possible way. You seem to have a reasonable grasp on the database bits, so, probably don't need too much additional help there.

With that in mind, would probably combine those tables, and might have "profile type" as part of the unique key of occupations - mainly to restrict the professionals to just the medical / pharmaceutical industry if you think that is necessary.

As far as SQL views are concerned, they are great for retrieving data, and can be a bit more ordinary for updates - there are some fairly stringent rules that do apply to an updateable view, so would not bother with those. I think calling (or executing) a stored procedure would probably be most desirable. You would create the stored procedure to do the inserts or updates passing the individual fields as parameters. Or, you could do the insert or update query directly if you wanted.

1)  tblRespondents                
    PK RespondentID
-- next three are required to acitivate an account
-- remaining could be held in their own table "tblRespondentProfile" with respondentID as PK
    Address (etc)
2)  tblProfOccupation              tblOccupations            tblGradeType
    PK ProfOccupationID            PK OccupationID           PK Gradetype       
    FK RespondentID                OccupationName            FK OccupationID
    FK OccupationID                                          GradeTypeName
    FK GradeTypeID
3)  tblMedicalConditions           -- look up table for Medical Conditions ?  Needed if you ever want to focus on specific conditions (probably big effort)
    PK MedicalConditionID          
    FK RespondentID               
4)  tblMedications                 -- look up tables for Medications ?   Needed if you ever want to focus on specific medications (probably big effort)
    PK MedicationID          
    FK RespondentID      
-- sample stored procedure - created once and used whenever needed....
create procedure usp_update_respondents (@respondentID int, @fname varchar(100), @sname varchar(100), @email varchar(100) )
  begin try
     begin transaction
       update tblrespondents set fname = @fname,
                                 sname = @sname,
                                 email = @email
       where responentID = @respondentid
     commit transaction
   end try
   begin catch
      if @@transcount > 0 rollback transaction
      print 'errors - bugga : ' + error_msg()
   end catch
-- then to use it you would execute that stored proced as per any query...
execute usp_update_responents 1,'Mark','Wills','yes_it_is_i@myisp.com.au'
-- and for the insert then you would do something similar, just return or select the ID just added... 
-- And use identity fields so that the numbering is taken care of, and after inserting, get the value of that new number using SELECT SCOPE_IDENTITY()
-- but that is a different question...

Open in new window


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

873 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