4 Replies Latest reply on Oct 5, 2012 11:18 AM by andrewtraub

    One-to-Many-to-Many?

    andrewtraub

      Here's what I'm trying to track:

      Each Matter can have multiple Clients (frankly, each client can have multiple matters, but that's not relevant here).

      Within each Matter, each Client can see multiple Doctors.

      For each Doctor seen, there will be multiple Visits by that client.

       

      Setting up the Clients portal with the Matter won't be a problem and has been done with just a picker to add.

       

      The design decisions next flow from how the Doctors and Visits are then handles.

       

      Should the Doctors be in a one-to-many relationship with the Client or with the Matter? I'm trying to display everything on one dashboard, so was have so far tied them to the Matter (in the database, there table contains a foreign key for the MatterID, ClientID, and DoctorInfoID). I think this is workable, but when entering the Doctor the user has to also enter the Client. This seems a bit awkward as ideally I would set up the selection of the Client, then selecting him/her would then allow me to add Doctors in another portal, and finally selecting a Doctor would allow me to enter visits in another portal.

       

      Any suggestions would be appreciated. Is the Bonsai Gardens solution in the Developers Kit the best place to start?

       

      Thanks,

       

      Andrew

        • 1. Re: One-to-Many-to-Many?
          comment

          Let me start at this point:

           

          andrewtraub wrote:

           

          Each Matter can have multiple Clients (frankly, each client can have multiple matters, but that's not relevant here).

           

          How can it not be relevant? If a client can have multiple matters, you will need a join table between the two - and that affects everything that follows: for example, does a visit of a client at a doctor belong to a specific matter?

           

           

          The other point that's not clear to me is this:

           

          andrewtraub wrote:

           

          Within each Matter, each Client can see multiple Doctors.

          For each Doctor seen, there will be multiple Visits by that client.

           

          Are there  assignments of a doctor to a client that need to be tracked? Or is it sufficient to track only actual visits made?

          • 2. Re: One-to-Many-to-Many?
            andrewtraub

            Sorry, to be clear, Clients are joined to Matters in a one-to-many relationship and that's taken care of.

             

            Yes, a Visit belongs to a particular Client, Doctor, and Matter.

             

            Currently, it looks something like this:

             

            Matter->1-To-Many->Client->1-To-Many->Doctor->1-To-Many->Visits

             

            Andrew

            • 3. Re: One-to-Many-to-Many?
              comment

              andrewtraub wrote:

               

              Currently, it looks something like this:

               

              Matter->1-To-Many->Client->1-To-Many->Doctor->1-To-Many->Visits

               

              Do you mean:

               

              Matters -< Clients -< Doctors -< Visits

               

              That doesn't look right. Surely, a doctor can see more than one client?  How about:

               

              Matters -< Clients -< Visits >- Doctors

               

               

              ---

              P.S. You haven't really answered my second question.

               

              Message was edited by: Michael Horak

              • 4. Re: One-to-Many-to-Many?
                andrewtraub

                You are right