Table Relationships for Invoices/Invoice Line Items/Discounts
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.