10 Replies Latest reply on Aug 3, 2017 9:40 AM by philmodjunk

    Rebuilding-Relationship Advice


      ~Xpost from other forum~


      Hello forum.  After upgrading (finally) to FM Adv 16, I've started a rebuild of my database.  The current one has been in use for about 4 years, works really well for me (single user).  I had a few things that weren't quite right in my mind, so I'd like to fix them with this rebuild.


      This is a database for a heating oil delivery/commercial fueling/HVAC  business.

      The relationship question is about how to handle different types of customers, and their different attributes.   Current design has all customers in one table, and therefore attributes that apply to one type don't apply to the other type.  I figure it's not best to have 30+ fields in a table that apply to only one type, and 15 that apply to only the other type.

      I also manage customers with a table for Interactions-email, newsletters, bill collecting.


      The first type of customer is a Heating Oil delivery customer.  Heating oil customers usually only have one location, with related equipment (one or more).  The second type is  a Commercial Customer.  Commercial customers have related jobs in different locations.


      Attributes that are similar:

      -Name,  delivery address, phone number, email address, etc.-basic info

      Attributes that differ:


      Heating Oil

      -Tank Size

      -Usage tracking (about 20 fields)

      -Deliveries made (about 10 attributes)


      -Service performed on the equipment.

      -Automatic Delivery scheduling

      -Invoicing for deliveries and service repairs & related products

      -Interactive map to show location and nearby Heat customers.


      -Related table of jobs and sites (about 10 attributes-different from Heat customers).

      -Deliveries made (about 5 attributes-different from Heat customers).

      -Different interactive map to show location and nearby Commercial customers.


      So the questions arise out of reporting and some of the questions I have are:

      1.  Should they be in 2 different tables?  If so, I guess they would each need their own related tables for deliveries made, and for interactions.

            a) Or would I connect them to the same tables as 2 different TOG's.

      2.  If they are 2 different tables, do I then create another TOG and attach both to an Invoicing Table.  Then I would guess it would make reporting much easier as I could report on all deliveries from the Invoice table with sub-summaries by name and type of delivery.  This is basically what I do currently, but am using QuickBooks.  I'd like to create the reports all from FM as I can use the Interactions table to generate all needed correspondence.

      3.  Or, should I keep all customers in the same table and make one-to-one relationships of their unique attributes, reducing the number of fields in the main customer table?  This I feel would be more like the relationship from Commercial Customers to Jobs would be Jobs would be a sub account of the main account.

         a) If so, do I then go with anchoy bouy and multiple TOG'S?


      Another thought would be for Commercial Customers, make one main record for each job, something like:

      -Acme-South St. Philadelphia

      -Acme-Sproul Rd. Broomall

      Then by having a one-to-one of related attributes, reporting would appear to be easier.


      Well thanks for reading.  If you need more detail, I'd be happy to supply.

      Any thoughts, comments always appreciated.



        • 1. Re: Rebuilding-Relationship Advice

          There are 3 different approaches that I see used for this issue:


          Your current setup: all in one table with fields that do not apply left blank. 30 fields used for one type and left blank for the other plus 15 fields used for the opposite type and left blank for the first isn't too terrible, but we are all in a "get every table narrow to improve performance" mode and for some devices, WANS and large sets of records, the current set up could be a bit slow. But the fact that you have all customers in the same table does make for easier reporting should you need to build reports that include data from both customers. If you ever have a customer that falls into both categories, this is generally also not a big deal with this approach.


          Two Separate tables: If there is even the slightest chance that you will need to build reports with data from both types of customers or that a customer might fall into both categories, then this option will be more difficult to work with.


          But there's a compromise approach: 3 tables.

          Set up one table with all the fields needed for both customer types. Link in two additional tables, one for each customer type and put the fields specific to one type into one of these two related tables. On a layout you can include fields from two of these tables and it will look to the customer as though you have just one table with those fields, but now, in reports, you can still include data from that single customer table for both types of customer and should you ever get a customer that needs to be recorded as both types, you just complete the fields in all three records.

          • 2. Re: Rebuilding-Relationship Advice

            Thanks again (as always) Phil for your response. 


            Some more relevant info I left out:

            Heat Customers have no related records to Jobs.

            Commercial Customers have a one to many relationship to Jobs.  So multiple related jobs records for one customer reside on a different table.

            So recording deliveries and tracking scheduling is only being done for HEAT Customers, off the CUSTOMERS Table, related to DELIVERIES Table.  Scheduling is done by comparing weather & usage.  Scheduling is only ever needed for HEAT Customers.

            I only need (want) to add a connection between JOBS and DELIVERIES.


            I did try building option 3 but still can't resolve the issue in my mind of 1 Commercial Customer having multiple Jobs records, getting multiple deliveries vs. 1 Heat Customer who has no Jobs record, but gets just multiple deliveries.

            Would (should) I always auto create one Job for Heat Customers, matching the  Customers---<Jobs relationship for Commercial Customers?  Then work off of the Jobs table for all scheduling and recording of deliveries.

            In my mind the relationship may look like


                                   HEAT_Attributes---(one to one)----CUSTOMERS--------------<JOBS---------------<DELIVERIES

            COMMERCIAL_Attributes---(also one to one)----CUSTOMERS


            (same CUSTOMERS TABLE, not 2 TO's)



            Now if that is correct, then I guess I tie Invoices to Customers, and can reference the different or multiple Jobs while creating an Invoice.



            Do I have to use more of an anchoy-bouy with multiple TO groups, and keep the two types of customers separate?


            Thanks again for your help.


            • 3. Re: Rebuilding-Relationship Advice

              This is one of those cases where the same "cat" can be "skinned" a number of different ways.


              You could have different table occurrence groups groups for your two different customers. Your anchor TO might be the same, but your buoy TO's would reflect the differences you describe.


              Reports based on Deliveries can be constrained to one type of customer (if you need that Jobs data) or include data from both. And your Bouy layouts can employ a number of options for automatically limiting Found sets to specific customer types.


              Other approaches can also work so if you are reading this, feel free to chime in!

              • 4. Re: Rebuilding-Relationship Advice

                Thanks a bunch Phil.  It's a real head scratcher for me because as I see my way down each path, there are a few things I like and a few things I don't like in each approach.

                I guess I'm going to have to build it and see...then maybe rebuild it and see. 

                Thanks again


                • 5. Re: Rebuilding-Relationship Advice

                  Fortunately, FileMaker makes that approach very easy. I generally view the solution design process as a cycle:


                  Map out the basic data model and interface, build it, stop and rethink, make changes, get user feedback, go back to the basic design and make changes....


                  Note that I don't see major issues with number 3, but then that's why I recommended that approach.






                  I'd definitely use UUID's for the primary keys here so that you don't have to worry about a Jobs record linking to a Deliveries record for a commercial customer. (Though I know of "oldschool" ways to make it work with serial numbers too.)

                  • 6. Re: Rebuilding-Relationship Advice

                    Thanks Phil,

                    You posted this:






                    but it should be this:





                    However, this raises 2 follow up questions.

                    1.  So I guess you don't recommend that I create a single new Jobs Record for each Heat customer, like this?




                    Then I could select deliveries for all customer from one list view layout, making it easier to map, and print tickets.


                    2.  Would I then tie both of these to Invoices, like this?




                                                    -or this-



                                                            Invoices--<Line Items>------<Products






                    Sorry for the drawing, but I'm sure you get the idea.


                    Thanks again


                    • 7. Re: Rebuilding-Relationship Advice

                      So I guess you don't recommend that I create a single new Jobs Record for each Heat customer, like this?


                      There would be no need for such a "dummy" jobs record that I can see. Note the data model matches customer to deliveries in this case. Note also that I am recommending UUID's for the primary keys so that a Delivery record with an fk field that matches to customer ID will not mistakenly link also to a Job ID field in Jobs.

                      • 8. Re: Rebuilding-Relationship Advice

                        With regards to invoices, Isn't a delivery also a line item in your invoice?


                        If so, then you can link Deliveries to an occurrence of Invoices. Some developers would use a strict Anchor Buoy approach and use two occurrences of Invoices, one for each table occurrence group. (or a third TOG just for invoicing that links to a third occurrence of Deliveries.)


                        Others these days do not consider Anchor Buoy a good design choice and would link one occurrence of Invoices to one occurrence of Deliveries--either in the group for Commercial or the group for Heating customers. A Layout based on that occurrence can produce invoices for both types of customers unless you need Job Info on the invoice, then you need two layouts, one for each customer type.


                        Note that if a Delivery is just one kind of line item and there are other charges that you might add as a line item, then the data model and your overall design becomes more complex in order to handle that wrinkle.


                        Note that I wouldn't normally use one Occurrence of Invoice that links to both occurrences of customers or both table occurrence groups as that unnecessarily complicates the design. Even though the TO may read "Customers|Commercial", you can still access heating customer records from it for an invoice.

                        • 9. Re: Rebuilding-Relationship Advice

                          Thanks again...and again...and again for all the advice.  I'm going to give it a go over the next few days.


                          • 10. Re: Rebuilding-Relationship Advice

                            Hmmm, sometimes you don't see an issue until after you've added the reply.


                            I DO now see the complication that an invoice linked to Deliveries can't access both types of customers since one type will have a related job record completing the link to Customers and one will link directly to customers.


                            As I said earlier there are multiple options.


                            You could add a customer ID field to Deliveries for your Commerical customers so that a Deliveries record has match fields for both the customer and the job. A delivery record for a Heating customer would then leave that job ID field empty. I'd probably set up two match fields from Jobs to Deliveries--not because I need it to match Jobs to Deliveries but because it's a simple way to automatically add the Customer ID to the Delivery record and use one occurrence of customer from the context of Deliveries to access data for both types of customers.