8 Replies Latest reply on Jun 6, 2012 5:18 PM by Tucker

    Relationship Question

    Tucker

      Title

      Relationship Question

      Post

      I am creating a DB that consists of 4-tables. User, BankAccount, InvestmentAccount and Company.

      A User can create new records that relates to a BankAccount (one user can have many bank accounts, each being a separate record) or an InvestmentAccount (one user can have many investment accounts, each being a separate record).

      Then when sitting on one of the account records they can choose from a list of Companies (banks and investment companies) where company info is located (contacts, phone numbers etc) to tie the company to the account record.

      User connects as one-to-many to tables BankAccount and InvestmentAccount:

      pkUserID----<BankAccount::fkUserID

      pkUserID----<InvestmentAccount::fkUserID

      I want to connect tables BankAccount and InvestmentAccount to the Company table so different companies can be related to individual records in BankAccounts and Investment Accounts. There's no problem connecting one of the tables but I cannot connect both tables to Company.

      Perhaps there is a fundamental setup that's wrong with the tables?

        • 1. Re: NoFields
          philmodjunk

          What, besides the name, is different between a BankAccount record and an InvestmentAccount record? (You might be able to simplify your design by using one table for both sets of records.)

          Presumably, you want these relationships:

          BankAccount::companyID = Company::CompanyID

          and

          InvestmentAccount::companyID = Company::CompanyID

          There's no reason why you can't have both of those relationships, but FileMaker will intervene with a dialog box when you try to create the second of these relationships. IF the dialog asks if you want to create a second "instance" of Company click OK. FileMaker will produce this relationship for you:

          InvestmentAccount::companyID = Company 2::CompanyID

          And that will work just fine. Company 2 is a new "occurrence" of the Company table. It's not a new table, it stores all the same data--it's a new "label" for the same table so that you can create the relationship without getting  a 'cycle' between User, and Company. You can double clicke it to open a dialog where you can give it a more descriptive name if you want.

          If the dialog asks if you want to make a new instance of InvestmentAccount, click cancel, select Company and click the duplicate button (two green plus signs) to make the new occurrence and then use it for your second relationship.

          To learn more about table occurrences, see this tutorial: Tutorial: What are Table Occurrences?

          • 2. Re: NoFields
            Tucker

             Thanks again Phil. Your description of what FM tried to do was right on. I suspected there was a conflict between User and the child tables in the relationship. I was thinking that perhaps I needed a join table between User  and BankAccounts and InvestmentAccounts.

            Aside from that, yes I do need separate tables for the accounts because in stead of 2 account tables there are actually 16 tables that attach to user (they all work the same way though). I could have had 16 separate company tables but I prefer one that holds all of the companies. I will try the T.O. route.

            • 3. Re: NoFields
              philmodjunk

              in stead of 2 account tables there are actually 16 tables that attach to user (they all work the same way though).

              That sounds to me like you could have one table instead of 16 separate tables, but with an added category field to record what type of account is specified for each recorde in that table. Not only does this produce a simpler relationship graph, it makes possible a report listing data from what is now stored in 16 different tables.

              This assumes, that all or nearly all of these tables use exactly the same set of fields. The more differences there are between the tables, the less benefit there is for using a unified table---though you'll want to consider that option even then as you determine how you need your database to function.

              • 4. Re: NoFields
                Tucker

                Thanks Phil. I understand what you suggest and will have a serious look at the possibility of one large table vs 16 small ones. Some fields are similar and it would save a lot of T.O's. and effort indeed! Thanks for the suggestion. 

                • 5. Re: NoFields
                  philmodjunk

                  Keep in mind that it's not an either/or proposition. If you look at your tables and nearly half of the fields are the same, you can merge the two tables and use one of two options for the fields that are used in one table and not another:

                  1) Just include the fields, but don't use then with the other records.

                  2) Put those fields in a related "detail" table that were you create a related record and store the info on those records that need to store data in those fields. This can be done so seemlessly, that the user never knows the added fields are not in the same table.

                  • 6. Re: NoFields
                    Tucker

                     Each individual table only has a few common fields. The majority of the fields are unique. However, all the tables do relate back to Users (parent table) and all of the unique fields are not validated as required (NotEmpty).

                    There will be 16 layouts (one for each table that exists now). What I can do is base each layout on just the fields I need for that particular subject type (e.g. bankaccounts) and leave the rest of the fields off a particular layout. The only downside of this that I can see is that each record will have a lot of fields that are empty. The total number of records that will ever appear in the DB will be a small number - maybe a couple of hundred.

                    • 7. Re: NoFields
                      philmodjunk

                      I'm not recommending either way, but that's what I meant by option 1). In 2) you'd put those fields in related tables in a one to one relationship with the central table. Since you can put fields from a related table directly on the parent table's layout, this will look exactly like option 1), but the other fields are actually defined in other tables.

                      At a place where I used to work we used that structure for the manufacturing specs for products produced at that factory. Since the products produced, were very different, we had a central table of SKUs, description, etc, that linked to different related tables for the details unique to each type of item produced.

                      • 8. Re: NoFields
                        Tucker

                         Thank you Phil. I will have a look at the actual number of common fields and make a decision based on that. I do prefer this as compared to all the the table occurances and value list copies that I would have to make.