7 Replies Latest reply on Jul 20, 2016 6:41 AM by BillisSaved

    Can you take a look at my DB?

    vvkozmenko

      Hi,

      I have a simple database that consists of the three tables: (1) Standardized Patients, (2) Event, and (3) Scenario. I have connected them with a join table.

       

      Standardized Patients are actors that portray some medical conditions.

       

      OSCE - this is an event that has Standardized Patients portraying some medical Conditions (Scenarios).

       

      Scenario is a script for the SP.

       

      In the join table I tried to create a simple connection among all three tables.

       

      I am a little bit confused on how to optimize data entry into the join table so that it looks up the name of the existing Standardized Patients and Scenarios. This must be something very simple but I've got stuck there.

       

      Would someone be able to take a look at the DB that I am attaching and guide me in the right direction?

       

      Thank you in advance.

       

      Val

        • 1. Re: Can you take a look at my DB?
          itptechnical

          I'm confused, and I will honestly tell you that it doesn't take much.

           

          So what you are looking to do is have the layout OSCE JOIN TABLE used as data entry? So in the first three fields on the left you would want to put something along the lines of 1, 2, 1, which would then lookup the information from the other tables correct??

          • 2. Re: Can you take a look at my DB?
            BillisSaved

            Good afternoon vvkozmenko,

             

            I hope your day is going well. I find it helpful to determine the reference table (i.e. anchor) before determining layout design. It appears from your description that this would be the event table (i.e. OSCE). Would I be correct in assuming that one patient may exhibit different medical conditions at different events?

             

            God bless,

             

             

            Bill

            • 3. Re: Can you take a look at my DB?
              vvkozmenko

              Hi, BizPraxis,

               

              Thank you for your reply. Yes, you are right I want the following three things:

               

              1. In the Standardized Patient layout I would like to see in which events (OSCE) each SP participated and which Scenario they portrayed. In the attached solution I've done via use of portal.

               

              2. In the OSCE layout, for each OSCE event I would like to see what Standardized Patients were used and what Scenarios they portrayed. Right now I've done via portal.

               

              3. In the Scenario layout, for each Scenario I would like to see in what OSCE events they were used and what Standardized Patients performed them.

               

              Going through the "Missing Manual" book I followed their example of the join table (something that I understood OK but had hard time with practical application) and connected the tree tables with one-to-many relationships.

               

              The Standardized Patient table will need to be populated with the demographic information manually. This is not a big deal.

               

              The Scenario table will be populated with all the scenario details manually.

               

              The OSCE (event) table is a somewhat confusing to me because it will contain more than one Standardized Patient portraying more than one Scenario.

               

              I am not sure how to set up the OSCE layout and what is the role of the join table in the solution.

               

              If someone can help me to clear the things out that would extremely helpful.

               

              Thank you,

               

              Val

              • 4. Re: Can you take a look at my DB?
                vvkozmenko

                Hi, itptechnical,

                 

                Thank you for your reply. Yes, that DB that I've posted is a bit confusing. The most confusing part of it is the join table.

                 

                I've described what I am trying to achieve in the reply to BizPraxis.  I hope that explanation does not create even more confusion.

                 

                Again, thank you for your reply.

                 

                Val

                • 5. Re: Can you take a look at my DB?
                  BruceRobertson

                  vvkozmenko wrote:

                   

                  The Scenario table will be populated with all the scenario details manually.

                   

                  The OSCE (event) table is a somewhat confusing to me because it will contain more than one Standardized Patient portraying more than one Scenario.

                   

                  I am not sure how to set up the OSCE layout and what is the role of the join table in the solution.

                  This is a little hard to follow. You seem to be saying, you designed a system, you chose to include the OSCE join table. But you don't know why or what it should include. Where did the OSCE table come from? It may very well be the correct choice, or part of the correct choice.

                  Also, I note that the Scenario records appear to be about general instances of something (depression, abdominal pain) but then they include something patient-specific (age). It seem like patient age at the time of the scenario should be part of the join table. (My impression). Or will you have abdominal pain scenarios for 69 year olds; different abdominal pain scenarios for 13 year olds; different abdominal pain scenarios for 68.5 year olds; etc.

                  • 6. Re: Can you take a look at my DB?
                    vvkozmenko

                    Hi, BruceRoberston,

                     

                    Sorry for not explaining my goal clearly.

                     

                    For better clarity, let's call the OSCE table a different name - the Event table.

                     

                    In the Event there are several Standardized Patients (Actors) performing several Scenarios (Scripts).

                     

                    The Scenario describes the character to portray, and it is the character's age in this table rather than the Standardized Patient's (Actor) age. Based on the character's age and gender, we will go through the database and pick up a Standardized Patient (Actor) with the appropriate demographics.

                     

                    As I said before, most info in the Standardized Patient and Scenario tables will be entered manually. I just have not figured out how to set the portals so that information is shared between all three tables.

                     

                    I am attaching a zip file that contains two versions of the same dummy prototype application - one for Mac, and one for Windows. Mac app requires just drag and drop to your desktop. Windows version: the executable file needs to be in the same directory as the Libs and the Resources folders.

                     

                    The prototype application will give you an idea what I am trying to achieve.

                     

                    Thank you again for responding to my original post. I appreciate your willing to help.

                     

                    PS I could not find the attach function on this post, so I am posting a link to the shared folder in my DropBox

                     

                    https://dl.dropboxusercontent.com/u/38820546/SP%20DB%20Prototype.zip

                     

                    Val

                    • 7. Re: Can you take a look at my DB?
                      BillisSaved

                      Good morning vvkozmenko,

                       

                      Based on the information you've shared thus far, it sounds like you could think of your scenario as one that is similar to tracking student training events. That is, you have a student (Standardized Patient), a course (Scenario), and a training event (OSCE). The event table would serve as your join table and would contain additional information specific to the event itself (e.g., patient age, date, duration, etc...). If the event schedule already exists, then your current 4 table schema should work.

                       

                      If the Standardized Patient table contains actual actor information, rather than the information pertaining to the character they are portraying, then I would consider this table the foundation of you data base. In other words, you have actors, whose information is not likely to change significantly or at regular intervals, and a potentially infinite number of events and scenarios in which they can participate. I would begin by building a layout based on the Standardized Patient table and then add a tab control or slide control containing portals based on the other tables. So on the first tab panel you could have a portal based on the event table showing multiple rows of information related to the current Standardized Patient record. The next tab panel might contain a portal displaying a single record from the event table (OSCE) that allows you to create a new record.

                       

                      I hope this perspective is helpful, rather than the root cause of a migraine. Have a great day!

                       

                      God bless,

                       

                       

                      Bill