8 Replies Latest reply on Feb 1, 2012 10:00 AM by philmodjunk

    relationship question



      relationship question


      I am updating a database that was designed to keep track of  clients and their files.

      The current file scheme is clients ---< files  wich are related   using the  Client ID field (clients::kp_client ID = files::kf_Client ID)

      The problem is that for one type (of about 10 different types of files) there are two clients who share this file number. I've thought about adding a 2nd client field to a file record but than the  the problem becoms tracking information that is client/file specific.

      I am thinking I would need to create another file record and give it the same file number, the clients wants that, but than it becomes tricky with statistics, I worry that summary counts would be off.

      Any thoughts or suggestions you may have will be as always very much appreciated.


        • 1. Re: relationship question

          An alternative approach would be to use a join table as you now have a many to many relationship.


          Clients::kp_ClientID = Client_File::kf_ClientID

          Files::kp_FileID = Client_File::kf_FileID

          Now the duplication takes place in Client_File instead of Files so you still have one record for each file in that table. A portal to Client_File can be used to list files for a given client when placed on a client layout and can be used to list clients when placed on a Files layout.

          If you are unfamiliar with join tables, you may want to examine this demo file: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html

          • 2. Re: relationship question

            Where I get stumped is that each file has additional File Data that is stored in a seperate file and at present related to Files table via File ID.

            Clients -------- Files -------- File Data

            Now I would have Clients ------ Client Files ------- Files so  I need to add a Client ID to the File Date table but I don't know how to relate it back to Clients table.

            • 3. Re: relationship question

              Not what I am suggesting. What I am suggesting would be:


              You would not add a client ID field to the files table but instead add a new table that stores Client ID from Clients and File ID from Files to link a given client to a given file.

              If two clients, ClientID = 5 and ClientID = 8 both need to be linked to the same file (FileID = 6), you create two records in the Client_File table:

              One with ClientID = 5 and FileID = 6. One with ClientID = 8 and FileID = 6.

              You can find examples of how to set up layouts for managing this kind of many to many relationship in the demo file.

              • 4. Re: relationship question

                And if there are some specifics that are different in a certain file that is shared by multiple clients, other fields can be added to the Client_File table.

                • 5. Re: relationship question

                  Thank you for being so patient Phil. I got it now. I had to add a Client ID field to the third file (File Data) that stores file and client specific data and than relate it to to Client_File table via Client ID and File ID field instead of  realting Files ----<FileData Tables as it was before.

                  • 6. Re: relationship question

                    Hmmm, sounds like you could use File Data as your join table then.

                    • 7. Re: relationship question

                      Yes, I see what you mean. But I think this way will be "cleaner". Now I run into another problem. How do I display the 2nd Client on the same layout (based on CLient_Files). I did a script that will switch between them. But can't figure a way how to show the other one on the same layout. I would like the user to see at a glance if the 2nd client for this client is added yet or not yet. If there isn't I got a button triggered  script that will add it. If there is they should see and they can use a button to switch between them. I belive I need to use a portal but can't figure  which TO to use and how to relate it.

                      • 8. Re: relationship question

                        Why would this be a layout based on Client_Files? Usually, you would base a layout on either Client or Files.

                        Client_Files is your join table or are we using different names?

                        From the Files layout, you can add a portal to the join table to list all clients linked to that file. You can add fields from the Client table to the portal row to include additional info such as the client name.