3 Replies Latest reply on Sep 10, 2009 12:09 AM by FentonJones

    Relationship Graph design: Advice needed



      Relationship Graph design: Advice needed


      Hi there,

      I've just designed a solution made of 9 essential tables to help a
      friend manage his customers. My friend owns a moving company and wants
      to be able to track his daily transactions in a database.

      Here are the main tables:



      One customer can book many jobs (orders), but a job can only be assigned to one customer at a time.

      An invoice can receive many payments, but a payment can only be applied to one invoice at a time.

      My questions:


      1) is my Relationship graph OK?

      2) How should I relate the refunds table to the rest of the graph?

      How should I best design my graph if necessary.


      Relationship graph

      Thanks in advance       <!--   POLLS   -->

        • 1. Re: Relationship Graph design: Advice needed

          1. Job to Invoices should be on Job ID, not Invoice ID, Expenses also on Job ID. Generally you do not put the "child id" in the "parent" table. It limits you to one child, and is basically backwards. Same with Invoice to Payments, use Invoice ID. You did Customers to Jobs, and Job to OrderLines right, so that is the method. 


          2. Good question. I think there's 2 different ways to do a refund.


          a. It could be a separate table, as you've done. But then it would be from Invoice to Returns, on Invoice ID, nothing much to do directly with Payments. You'd subtract the Refunds total within an Invoice calculation.


          b. Or, a "refund" could be just a "negative payment". You would enter it as a negative number in Payments, with a Type field which had "Refund" in it. It is generally easier to separate similar things within a table when necessary than to deal with 2 separate tables. Some kind of auto-enter and/or validation could make sure it was entered correctly. ("Refund" must be negative, negative auto-enters "Refund", or at leasts warns you.)


          Either method has its pros and cons, and I'm not really a "retail expert" (though somehow I seem to do a fair amount of it :-). But the "negative payment" method allows you to show the refunds in a report using the Payments table.


          c. With a controlled completely "transaction" data entry interface, I suppose a refund could be both. But the separate one would be kind of redundant. 


          • 2. Re: Relationship Graph design: Advice needed




            Thanks a bunch for the clarification.


            Below is an updated "ERD", what are you thoughts?


            3 more questions if you don mind:


            1 - How should relate the employees table (separate file) to the rest of the graph?

            2 - To make sure i get this properly, the other refund option was to create a "refund" field within the PAYMENTS table and use negative numbers?

            3 - What do you think of the relationship between expesnes and jobs? I had to do it on a separate joint.



            Thanks again! 


            • 3. Re: Relationship Graph design: Advice needed

              Short answers, as I already should be in bed :-] 


              1. Not sure what you mean by this. You have Employees separate. I would recommend them having their own "table occurrence group" (TOG),* disconnected, with Employees as the anchor. That way you can do things like "show an employee the jobs they are working on, the customers they are talking to, appts., to dos, as things are (or may be) added, without clogging up the basic "customer-jobs-items" TOG. 


              But another table occurrence (TO) of Employees may hang off of Jobs. You might want a "join" table between them. That would be for multiple employees working on a job. But we don't know.


              Basically, sooner rather than later, anyone helping you is going to need more info. There is not just one way to do a relationship that fits all businesses. There are variations, mostly in complexity, which require somewhat different graphs. But they always follow the same principles.


              2. Yes, a refund can be thought of as just a negative payment, going out instead of coming in. It keeps the arithmetic in one place. The are both the customer's money. A Type field can be used to separate them, when that is necessary.


              3. I don't really see why you'd bust Expenses off like that. While I sometimes advocate separate TOGs for different "entity" groups or operations, I don't see it in this case. Unless Expenses is a complex operation, with several other tables also needing to come into play. Once again, we have no idea of your business. 


              * This whole separate TOGs on the graph can be part of a popular method of Relationship Graph organization which is known as the "anchor-buoy" method. It is what I use. Whether you use it or another variation, a consistent organization-separation method, with a clear (to you anyway) naming convention is required for complex solutions. There is a good PDF here on FileMaker's site:

              FileMaker Development Conventions