14 Replies Latest reply on Aug 30, 2013 6:06 PM by drew

    Newbie Questions

    drew

      I am a solo healthcare provider and am interested in developing a solution for my practice to help in managing patients. I am new to FMP (despite having joined this community many, many months ago, I have not started developing a solution until now) and have (4) questions:

       

      1. Contact table(s) – In my practice there are three types of contacts that form the type of relationships in my practice: (a) patients; (b) healthcare providers, and; (c) other contacts (e.g., insurance agent, printer, landlord, etc.). Would it be wiser to have three contact tables, or one contact table with a field for 'contact type' to differentiate the various types of contacts? What would the pro/con of either method be? What is the advantages/disadvantages of having one contact table vs. three?

       

      2. Healthcare Measures – In my practice, I have patients fill out various paper/pencil healthcare measures (e.g., pain, sleep, mood, etc.). Eventually, I would like to substitute paper-based measures with something on an iPad or even through a secure web portal. I want to track this data and graph it as well. Currently I use a spreadsheet. Would it be better to have one table of all the healthcare measures (and then simply pull which data fields I need in a given view), or one table per healthcare measure?

       

      3. Right now, my healthcare practice is myself. I do everything. I may in the future however, add an administrative assistant—or even another healthcare provider (that is less likely over the next year than it is to have the admin support person however). The admin person has no need to see patient information, etc. I realize I can control that via permissions. However, would it be easier in the long-run (assuming I were to grow my practice into a multi-provider/many employee practice), as well as for building something relatively quickly, to have a single file solution versus using a separation model? Is it possible within a single file system to have say, the admin person log in and see a different view in a single file system than what I would see as the owner/provider? I am aware in the separation model that would be doable.

       

      My goal is flexibility to develop the database quickly and easily (maybe an oxymoron) if I should take my practice in a different direction, yet have something very usable and up and running right now for myself.

       

      4. Finally, I purchased FMP 12: The Missing Manual. I am considering also purchasing the FM Training Series, but was wondering if The Missing Manual basically covers everything the Training Series would and therefore would add little value.

       

      In advance, many thanks for the help!

       

      Drew

        • 1. Re: Newbie Questions
          Mike_Mitchell

          Hello, Drew. Welcome to FileMaker.

           

          Your questions are common to development (don't know if that's a comfort or not).      You can probably find previous threads where they've been discussed if you search. The point being, you'll likely see a difference of opinion on these from experienced, equally qualified developers. But, here's my take:

           

          1) Whether to segregate different types of similar entities into different tables largely depends, in my mind, on how similar they are. There's a data normalization principle that says you shouldn't leave any field empty on any record. In the real world, that rarely happens, but it does give us a clue: Your table shouldn't have a lot of unused fields. So if your different kinds of entity are very similar to each other, where you can share lots of the same fields and have few, if any, fields dedicated to each one, then sharing a table is a good idea. On the other hand, if they're significantly dissimilar, where you have big chunks of fields that apply to one but not the others, then they probably belong in different tables.

           

          There are other considerations with this, too. When a user does a Find All, he gets all records in the given table unless you do something to prevent it (like a Custom Menu that hijacks the Find All command). So if, for example, you want to make sure someone doing a Find All doesn't blend different types of contacts together on a report, then that becomes a consideration in your design.

           

          Another aspect is maintainability. What happens when I add a new contact type? Do I want to add a new table to my structure (along with the necessary layouts, scripting, etc.)? Or do I just want to change a pull-down (either directly in the value list definition or via a domain table)?

           

          One strategy you can use to help with this is the use of a related entity - attribute table. In such a design, you define fields that don't have a fixed purpose; rather, you define field pairs in related records, where each record has one field that tells you what the record means, and one field that tells you its value. (Example: Field A = "Phone, mobile"; Field B = "888-555-1212") The big advantage with this sort of arrangement is flexibility. The disadvantage is you have to do a lot more scripting to manage it and reporting becomes more difficult.

           

          2) For collecting data such as what you're describing, I would actually recommend you go with an entity-attribute sort of model. Each record contains the attribute you're measuring ("pain level") and its value (1 - 5). These tie back to the patient evaluation via the patient evaluation key. This gives you the flexibility to add new measures as you change the intake rather than having to change the data model. I would recommend you set up a "template" of records and then just copy that template into each intake (via Import or using some form of new record creation through a portal); that will allow you to predecide what the allowable options would be.

           

          3) For controlling user access, the answer between single file vs. separation model is ... yes.      You can control accesses in either case. Using functions like Get ( AccountName ) or Get ( AccountPrivilegeSetName ), you can easily determine what accesses the current user should have. Once you do that, you can script what layouts the system will navigate to quite easily.

           

          In fact, I would generally recommend that, if security is your only reason for going to separation model, that you skip it. Separation model offers a lot of benefits, but managing security becomes a bit of a nuisance because you have to do it across multiple files. I don't recommend you just rely on the user having the "correct" interface file; that's inherently insecure (what happens if the wrong interface file winds up on the wrong computer?). You still need to manage accounts properly across all the files. It can be done, but ... if it's your only reason for going separation model, I wouldn't. (If, on the other hand, you want to gain the other benefits of separation model - like managing small updates without having to migrate data, or splitting up your Relationships Graphs for better performance, then the view is worth the climb.)

           

          HTH

           

          Mike

          • 2. Re: Newbie Questions
            RonSmithMD

            What kind of practice?

             

            Ron

             

            Ron Smith, MD, 'The Pediatric Guide For Parents'

             

            Want to know more about me and my family? Take a look at the free ebook about my daughter below.

             

            Forever And A Day For Laura Michelle

            • 3. Re: Newbie Questions
              keywords

              Just adding one other consideration to the issues covered by Mike—duplication. If you have separate tables for different categories of contacts, as soon as a contact fits more than one category (a physiotherapist becomes a patient, or you engage a patient to mow your lawns, or your landlord, an art dealer, is also a patient, and you engage her/him to supply artworks for your waiting room, etc) you have multiple instances of the same person, and have to manage that.

              • 4. Re: Newbie Questions
                drew

                Hi Dr. Smith.  The practice is a behavioral medicine practice.

                 

                Drew

                • 5. Re: Newbie Questions
                  RonSmithMD

                  Do you do regular physical exams too? Labs like UAs and CBCs in your office?

                   

                  Ron

                   

                  Ron Smith, MD, 'The Pediatric Guide For Parents'

                   

                  Want to know more about me and my family? Take a look at the free ebook about my daughter below.

                   

                  Forever And A Day For Laura Michelle

                  • 6. Re: Newbie Questions
                    Mike_Mitchell

                    Excellent point. Which does bring up the other issue about a field that determines what kind of contact it is. If you do have the possibility of multiple categories for a contact, you'll need to define a table with Contact Type and allow a one-to-many relationship so each contact can have more than one Contact Type. Otherwise, you'll run into the same issue regardless of which method you choose.

                     

                    Mike

                    • 7. Re: Newbie Questions
                      drew

                      Hello Ron.  No. I am a health psychologist; my practice is limited to behavioral medicine.  I specialize in behavioral sleep medicine and chronic pain management tx.

                       

                      Drew

                      • 8. Re: Newbie Questions
                        davehob

                        Hello Drew,

                         

                        Re. books, I think the Missing Manual is excellent, and I would recommend complementing it with the "FM12 Developer Reference" (Bowers, Heady, Lane and Love), which is a really useful manual.

                         

                        I've also found the FM training materials on VTC.com and Lynda.com extremely useful, and, of course, this forum - consistently supportive and (usually!) non-judgemental.

                         

                        Dave.

                        • 9. Re: Newbie Questions
                          drew

                          Hi Dave.  Thank you very much for these suggestions! 

                           

                          Have a great weekend!

                           

                          Drew

                          • 10. Re: Newbie Questions
                            drew

                            Ahh...That's very helpful.  Yes, it actually IS possible to have multiple categories for one contact!  I didn't even think of this when conceptualizing this design in my head.....In fact, I already have had several healthcare providers who also became patients too.  Thank you again! 

                            • 11. Re: Newbie Questions
                              drew

                              1.     Thanks so much Mike for your thoughts!  :-)  I wanted to f/u with your suggestions for clarification.  Overall, if I am understanding you correctly, it would seem since healthcare provider contacts and “other” contacts are most similar in that these two types of contacts have nearly identical information (e.g., they would have a field for organization, contact person(s), cell phone, office phone, fax phone, address, etc.), that might make a good case for one table for both types of contacts.  The only difference between these two types of contacts, is the type (e.g., for providers, internal medicine, rheumatologist, etc., vs. printer, insurance agent, landlord, etc. for “other” contacts).  Nearly all fields might be filled for these two types of contacts. 

                               

                                On the other hand, for patients, they would not have an organization, fax #, and possibly other data fields.  Does this make sense?  BTW, what is considered “a lot of unused fields?” I ask because, there does seem to be a difference between two of the three types of contacts, and therefore would have some fields that don’t match, but not a lot.

                               

                              Is there any reason why I might want to separate the types of contacts into different tables, i.e., “other contacts,” healthcare provider contacts, and patient contacts?  Do I gain anything by doing this (vs a pull down menu, etc)?

                               

                               

                              2.     This is a great idea.  I had already considered how helpful it would be to have a template that provided flexibility to add different measures right in the middle of the clinical consultation, as things came up.  This sounds like what I need to do.  In fact, I was thinking even more specifically during the consult:  Sometimes a patient is referred for one thing, but as the initial diagnostic evaluation proceeds, the patient reports information that dictates that the evaluation pivot to include additional info not initially obvious.  For example, I do a lot of sleep work and receive lots of referrals just for sleep.  So initially, I have a template for sleep evaluations but find that the patient complains that chronic pain is a significant precipitating factor impacting their sleep.  That piece of information means I need to pivot and now also include in the sleep evaluation, lots of information about their pain--because I also treat pain and will need to address the chronic pain clinically before I treat the sleep problem.  If I understand what your suggesting Mike in the use of the entity-attribute model, this would be the way to go for this as well.  Am I understanding this correctly?  Finally, would there be any advantage to having a child-table off of the the patient contact table?  Someone mentioned to me that since there will be multiple measures for any one given patient,  and need to be segregated specific to a patient for both person alignment and HIPAA privacy that I ought to think about going this direction. I’m a bit lost on that one.....Also do join-tables fit in this equation regarding measures and patients? 

                               

                               

                              3.     With regard to the data separation model, while I was aware of the security benefits, I was thinking more along the lines of what each user see’s on their computer, based upon the role the user has in the organization--in other words, the user GUI.  For example, an admin support person that only answers the phone and inputs data related to adding/modifying patient info would not need all the additional tabs, buttons, etc. that say, I would as a healthcare provider.  So I was thinking that perhaps the separation model would facilitate this easier.  However, I’ve learned that setting up this model has it’s own issues as well and aside from very specific reasons, is not ordinarily needed---that in fact, an unseparated model is easier to maintain and create.  So if my understanding of this model is correct, I guess my follow up question  is if there is a way to address the user GUI without the necessity of a separation model and are there other advantages/disadvantages to NOT using the separation model.  Finally, if I don’t use a separation model, is it a problem/big pain in the butt to split things out in the future should it become glaringly obvious that using a separation model would be more efficient, etc.?

                               

                              Many thanks Mike for all of your input. 

                               

                              Drew

                              • 12. Re: Newbie Questions
                                keywords

                                Another important consideration, especially in your practice, is confidentiality. You would not want the plumber's health record exposed to the wrong people just because he is also a patient! To me a logical structure would be to use your contacts table to hold common contact elements (names, addresses, phone nos, etc), and have patient records, service records and supply records in separate dedicated tables. Just some further thoughts, but no doubt you've already thought them yourself.

                                 

                                Cheers!

                                • 13. Re: Newbie Questions
                                  Mike_Mitchell

                                  Drew -

                                   

                                  1. "A lot" is ... more than "a few", which is less than "many".  

                                   

                                  In other words, there are no hard and fast rules. This is where you get into the difference between theoretical relational modeling and relational modeling in the real world. However, as a general rule of thumb, for performance reasons, a table should probably not have more than about 3 dozen or so fields in it. More than that, you're probably looking at a problematic data model. But again, this is just a rule of thumb; there are always exceptions.

                                   

                                  In your particular situation, I don't really see a benefit in separating your contacts into different tables.

                                   

                                  2. Yes, I believe this is the correct model to follow. You have a record for each measure, a field describing what the measure is and another field recording its value. Note that, in some cases you may need more than one field for the value, since you might have to record a numeric value, a text value, a date, and so forth.

                                   

                                  And yes, you'll need to tie the measure to the patient, but probably not directly. More likely, you'll tie the evaluation to the patient, then the measures to the evaluation, like this:

                                   

                                  Patient  ---<  Evaluation  ---<   Measure

                                   

                                  No, you shouldn't need join tables, because all the relationships are one-to-many. Join tables are used where you need a many-to-many relationship.

                                   

                                  3. Since the GUI is intrinsically linked to the user's access levels (at least in the model you're suggesting), you can't really separate the two. So the issue of getting the wrong interface file on the wrong user's computer still exists, even if you're only trying to customize the UI. So no, there's really no big advantage, at least in my mind, of using the separation model for this particular scenario. There are some who disagree, and I respect their opinions; it's less of an issue when everyone who has the same role works in the same area or department, for example.

                                   

                                  That said, you can certainly control the UI in a single file in more or less exactly the same way you do in a separated model: you just create separate layouts for each access level or role and program your navigation to take the user there as appropriate. In fact, given that you can use the Go to Layout script step using a calculated name, it's relatively simple to adopt a naming convention for your layouts that uses the name of the role (ex: Data Entry - Accounting) and then use a calculation such as "Data Entry - " & Get ( AccountPrivilegeSetName ) to tell the system what layout to use.

                                   

                                  As far as moving from a single-file model to a separated model, it's not really that hard. It's basically a matter of duplicating the file and then stripping out all the data tables from the interface file and stripping out the interface from the data file. Not very difficult at all, really. The primary issue revolves around cleaning up the Relationships Graphs, since there will likely be unnecessary elements in both after the separation (especially in the data file), but that's something you can do at your leisure.

                                   

                                  HTH

                                   

                                  Mike

                                  • 14. Re: Newbie Questions
                                    drew

                                    Many thanks Mike for your time and generosity answering the myriad of questions I have. 

                                     

                                    Have a great weekend!

                                     

                                    Drew