AnsweredAssumed Answered

Table Relationships for Invoices/Invoice Line Items/Discounts

Question asked by jared944 on May 18, 2014
Latest reply on Jun 10, 2014 by jared944


Table Relationships for Invoices/Invoice Line Items/Discounts


     Hey everyone,

     I am having difficulty figuring out a relationship for a database I am trying to establish and want to see if anyone has any ideas to jump start my creative process.

     I have five tables - Invoices, Invoice Line Items, Products, Promotions and Promotion Line Items. (Enclosed is the relationship graph).

     I currently have a working invoicing database ; now I want to try to add discounts based off of promotions.

     Each row in the Promotion table contains a 1) "Promotion ID" per line item, 2) "Group ID" which is the name/ID of the promotion, 3) a related keyfield (with the Products table) that identifies the product, and 4) the amount to discount.

     I want to be able to select the promotion (IE "Group ID" ) to apply from the Invoices Layout from a portal showing the Promotion Line Items table. I then want to set up a relationship that will identify if that promotion "Group ID" contains a product among the invoice line items. If it does, than I want it to apply the discount in the final invoice.

     I'm trying to be very careful to make it so if I change a promotion OR product amount in the future it wont affect prior Invoice Line Item amounts. I also want to look at each invoice/customer and see what promotions were used so I have the analytics to shape my future marketing.

     If anyone has any suggestions I would be very appreciative.