5 Replies Latest reply on Jul 14, 2009 12:51 PM by mrvodka

    Many to Many



      Many to Many


      I have a client database.  Among other things, I need to keep track of medication and dose that each client is taking.


      One client can take many different types of medication at one time.


      I do not want to have to enter the medication over and over again for each record in the client table, this seems redundant. 


      A separate table named "Medication" seems appropriate. The only field that would be in this table (besides Key Fields) would be one named "medication name"


      If I relate "Clients" table to "Medication" table it seems to this novice that I would have a many to many relation, i.e. one client can have many medications and each medication can belong to many different clients.


      Do I need a join table?  Am I going about this simple problem the wrong way? 


      Thank you for any help, this database stuff is giving me the need for medication. 

        • 1. Re: Many to Many

          You probably should use a join table here. Patients, Medicine, Dosage ( join ).


          The Dosage table can store the foreign key of the Patient, the Medicine, and perhaps the time and dosage amount.

          • 2. Re: Many to Many

            Wow that is the quickest response I have received to any question posted on the internet!


            In the "Dosage" table, I would just have  _kf_Patient_id, and _kf_Medicine_id  and one field named dosage?


            When I create the portal on the Patient table, would I choose


            "medicine name" from the Medicine table?


            "dosage" from the Dosage(join) table?


            And finally _kf_Medicine_id from the Dosage(join) table?


            Hope this makes sense


            And a Huge thanks! mr_vodka 

            • 3. Re: Many to Many

              Yes. You can use a popup menu to show your medicines. If you use the option to show the second field as the name and the ID as the first field, when you choose the item in the portal record, it will store the Medicine ID. You can also have the dosage field be a free entry field or you can use another value list... your choice.


              BTW, make sure that you turn on allow creation fo records to the dosage table ( on that side of the relationship ) from the patients table occurrence if you want to use the portal to create the related records. 

              • 4. Re: Many to Many

                I am truly thankful for your help!


                It might not seem like much to you, but you helped me over hurdle, that perplexed this novice.


                Thanks again!! 

                • 5. Re: Many to Many
                     you are welcome. :smileyhappy: