9 Replies Latest reply on Dec 18, 2013 12:32 PM by philmodjunk

    One-to-One Relationship

      Title

      One-to-One Relationship

      Post

           Are there any inherent advantages or disadvantages to creating a table which will have a one-to-one relationship with another table?  A search of the Forum didn't seem to any return any results for one-to-one relationships.

           The new table would likely have some 100-150 fields and would need to be consolidated every year and would hold primarily historical data that would not be heavily utilized.

        • 1. Re: One-to-One Relationship
          philmodjunk

               100-150 fields is a lot of fields for a same record. That may be necessary, but sometimes, it indicates that a different data model would be more efficient.

               There can be reasons to link two records in a one to one relationship, but what you get is nearly the same function as having a single table with a very large number of fields instead of two related tables.

          • 2. Re: One-to-One Relationship
            RickWhitelaw

                 One to one relationships can be very efficient especially if the file is served. Viewing one record will only necessitate the downloading of one matching record. However this is quickly made meaningless by having 100-150 fields in one table, or in effect, per record. Ideally a table should contain the necessary PK and FK fields and only whatever else is central to the purpose of the table. I believe in "skinny" tables. Better to have dozens of small tables than one huge fat one. Ask yourself what purpose does each of the 100-150 fields have and if they can be split off into smaller tables. Sometimes this means devising schemes for record creation in these small tables, but it'relatively simple. Google Database + Normal and you'll find a lot of info about normalization. Well worth it!

            • 3. Re: One-to-One Relationship

                   Phil & Rick,

                   I agree, 100-150 fields is unusually large.  But the fields  contain monthly totals for 5 categories linked to a person.  And, I need two years of data.  That's a minimum of 120 fields and I can't think of a way around it; at least not yet.  And, the request came from the end-user, so I have to handle the situation with care.  Quarterly totals have already been vetoed.  I'll give it some more thought.

              • 4. Re: One-to-One Relationship
                RickWhitelaw

                     You may want to rethinkmyourcstructure. Phil might have something to say about this.

                • 5. Re: One-to-One Relationship
                  philmodjunk

                       I'd need more details to be more specific, but what you describe sounds made to order for a related table. Your 120 fields could be a 2 fields in 60 related records.

                  • 6. Re: One-to-One Relationship

                    Phil,

                         If this still interests you, I’ve attached a simplification of the structure.  First, a simple person table.  Related to the Donors table is the Donations table with a date and four categories of donations.  (Of course, there are many other fields in the actual application.)

                         My goal is to be able to display monthly totals for each of the four donations categories for each Donor. So that’s 4 categories for twelve months or 48 fields.  So, two years of data means 96 fields.  An important goal for me is that these totals be displayed and do not require a printed report.  I don’t want the end user to be forced to print a report to answer phone inquiries.  (It’s also my experience to be sparing in offering too many reports.  Many reports seem to be printed, filed, and forgotten.)

                         My original question was where to locate these summary values; within the Donor table or in another table (the one to one table) as shown below.  Your suggestion is that another option is perhaps available.

                    • 7. Re: One-to-One Relationship
                      philmodjunk

                           Donations should be re-designed. Typical fields for Donations might be:

                           DonationID
                           DonorID
                           DonationType (enter a value of "money", "Edible", etc to identify the type)
                           DonationAmount
                           Donation Date

                           Each donation then becomes a separate related record with far fewer fields than you have now.

                           And both summary and crosstab reports can still be generated from such a table--but now you are dealing with a much smaller number of records in a much more flexible format.

                      • 8. Re: One-to-One Relationship

                             Phil,

                             Thanks for the tip.  I can see the advantage of combining four or five donation types into one value.  However,  your last sentence says  "And both summary and crosstab reports can still be generated from such a table--"    That's exactly what I'm trying to stay away from: reports.  I would prefer that these monthly totals for each type of donation be displayed on each donor's screen along with other donor data.  So far, I have not learned how to isolate the total money, edible, etc.  donations made by Joe Donor during the month of October 2013 which can be displayed right along with Joe Donor's address, phone number, etc.

                             If this is possible, I would like to learn how.  If not, I'll have to change plans.

                        • 9. Re: One-to-One Relationship
                          philmodjunk

                               The methods used in such a "report" -- which in filemaker is just the name for a different layout, can be used to do what you want on your donor layout.

                               There are multiple options that can work. The best option can depend on the version of FileMaker that you are using, exactly what you see on the screen and whether or not you want to be able to edit the donation data shown in them. Both indivdual entries and summary totals are possible.

                               One options is to set up a row of filtered portals. Each portal can filter for a particular donation type and they can be arranged in rows and columns to show data for multiple donation types and date ranges (such as a particular month, quarter, year...)