12 Replies Latest reply on May 25, 2015 11:57 PM by keywords

    I need some DB design help

    docpanderson

      Below is a snip of the relationships table that I am setting up. 

      Background:  I am developing a database for my Chiropractic office.  I am using the starter solution contact DB as my patient list.  Now I am working on the patient examination.  I have the exam broken into the components you see below.  I have a script from the contact details layout that will open a new exam and give it a new number indicating a new exam.  My problem is that I don't want to have to enter an exam number into each table.  However, I am confused about how to link each table to the central Exam table.    Short of having one enormous examinations table that incorporates all of the sub tables is there a better way of doing this that I am not thinking about?

      Exam components.JPG

      Any help would be appreciated.

      Peter

        • 1. Re: I need some DB design help
          Mike_Mitchell

          Peter -

           

          There are two basic ways to automate the insertion of a foreign key. The first is to use a portal with the "Allow creation of records" checkbox checked on the child side (in this case, tables such as "Subluxations", "Posture", and so forth). Then, whenever you type information into any field in a blank row in the portal, the foreign key in the child table will automatically be inserted.

           

          The second method will involve scripting. There are a variety of means of scripting child record creation, including the use of a global set of fields (where you capture the data on one layout, move to a layout based on the child table, then write all the data into a new record, including the primary key of the parent record), or the so-called "processing in place", where you use a global field on the parent side that points to the foreign key on the child side (again, in a relationship with "Allow creation of records" checked). You can then use the Set Field script step to assign the correct values to the child record, which will automatically inherit the value on the parent side. An advanced version of this technique is known as the Selector-Connector model, developed jointly by Todd Geist and Jon Sindelar; you can find posts on the forum about it, or just Google it.

           

          HTH

           

          Mike

          • 2. Re: I need some DB design help
            erolst

            Let me add this to Mike's explanation:

             

            You use a key called "exam #" …this seems to be a “natural” key, i.e. it has meaning within your “business”. It is usually better to use an “artificial” key – which is totally within your control. (A good check is this question: is this a value that could ever appear on outside correspondence? If the answer is yes, don't use it.)


            Maybe use “examID”, like in the Patient patient. (It's confusing for other people/developers (and eventually for yourself) to have primary keys named “…id” in this table, “…#” in another.

             

            And while you're at it: try avoiding special characters (and even blanks) in field names. FileMaker is very forgiving with field names, but other platforms aren't – and you never know when you'll need to make outside connections (or just use ExecuteSQL() internally).

             

            docpanderson wrote:

            However, I am confused about how to link each table to the central Exam table.    Short of having one enormous examinations table that incorporates all of the sub tables is there a better way of doing this that I am not thinking about?

             

            One approach is the one used for surveys / questionnaires – which in a way an Exam is – where you use many records, rather than many fields:

             

            Exam (type) --< ExamExamination >-- Examination --< ExamDefinitionForType >-- ExamType

             

            • store all possible examinations in Examinations

            • define ExamTypes in ExamDefinitionForType

            • create a new Examand specify the type

            • have the appropriate Examination records (by type) copied into the ExamExamination table (which represents/combines all of your existing tables)


            Now each exam has just/exactly the necessary ExamExamination records.

            • 3. Re: I need some DB design help

              It might be best to put all of those tables into one table so you can make a report out of it.

               

              You are probably creating only one reacord in each table for each exam, right?

               

              Create a table with fields from each table and then a 'Category' field to be filled with the table names.

               

              In fact, you could put everthing into one table and save a lot of headaches for yourself.

               

              Are you going to create more than one record for the exam for Tautness? Reflexes? etc?

               

              I may be wrong but it looks like all of those tables belong in one table. If you will only add one record in each table for the parent table, they do go in one table not many.

               

              No one asked that question so I have to... 

              • 4. Re: I need some DB design help
                erolst

                jackrodgers wrote:

                No one asked that question so I have to...

                You may want to carefully read the previous responses before making such a statement…or posting at all.

                • 5. Re: I need some DB design help
                  electon

                  I agree with the model. This is the most flexible of all of them.

                  You can have templates where you can add / modify types of data you will want to collect in the future.

                  The only issue I think is: data types and how / where to store the data. If I understand erolst model correctly that is.

                  Text fields sort different than number fields, etc... so how you store the data can become an issue later when it needs to be viewed in "larger context".

                  This is overcome in an Entity Attribute Value data models, but can be very challenging.

                  • 6. Re: I need some DB design help
                    ibrahim_bittar

                    Hi Peter

                     

                    An example file is worth a thousand replies...

                    • 7. Re: I need some DB design help
                      docpanderson

                      What is an example file?  Sorry but I am a FM newbie.

                      Peter

                      • 8. Re: I need some DB design help
                        keywords

                        The file was attached to Ibrahim's reply. Download it and take a look inside.

                         

                        For what it's worth, I would suggest you consider using all the component tables purely to store the various options available (a Products table, you could say), then, since any relationship between Examination and any of these tables will be many-to-many, create a linking table to deploy specific treatments in any given examination (a Line Items table). This is really just stating in a different way what erolst has already suggested.

                        • 9. Re: I need some DB design help
                          erolst

                          electon wrote:

                          The only issue I think is: data types and how / where to store the data. If I understand erolst model correctly that is.

                          Text fields sort different than number fields, etc... so how you store the data can become an issue later when it needs to be viewed in "larger context".

                          Store the data type with the Examination record; in ExamExamination, have one field per data type; in the UI, display all fields and hide the non-appropriate one.

                           

                          Obviously, this is A Piece of Cake with FM ≥13, but indeed

                          electon wrote:

                          can be very challenging.

                          with older versions.

                          • 10. Re: I need some DB design help
                            erolst

                            keywords wrote:

                            For what it's worth, I would suggest you consider using all the component tables purely to store the various options […] This is really just stating in a different way what erolst has already suggested.

                             

                            Not exactly; I was suggesting to do away with all these sub-tables*, store all possible options / Examinations?** in one table and use a join table to associate them with different Exam types.

                             

                            (Unless each Examination can only occur in a single type of Exam, in which case one wouldn't need that join table, but could store the Exam type directly with the Examination record.)

                             

                            And yes, the ExamExamination and ExamDefinitionForType tables from my example are basically line item tables; the latter defining the blueprint that the former uses to describe an actual exam.

                             

                            * If that wasn't clear from my original post, here it is, re-stated.

                            ** not being an MD, I am unsure if that is the correct term

                            • 11. Re: I need some DB design help
                              siplus

                              Before you begin, ask yourself the questions you will be asking yourself in the future.

                               

                              You will discover that very common questions are:

                               

                              - What was this patient's weight last time he came ? How did it oscillate over the last year ?

                              - When did I evaluate posture and reflexes together, and what were the results ?

                               

                              make sure that form follows function and your schema makes it easy to answer such questions.

                              • 12. Re: I need some DB design help
                                keywords

                                I agree with the single component table; it would require just one extra field to define whether a particular treatment component type/purpose is for tenderness, tautness, posture, etc.