Database structure; working like a flowchart?

Posted on 2009-02-24
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 41

    Expert Comment

    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

    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 68

    Expert Comment

    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.

    Author Comment

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

    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

    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',''
    -- 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

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    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.

    754 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

    20 Experts available now in Live!

    Get 1:1 Help Now