For purposes of simplicity, imagine you have these four tables:
Sales
Credit Memos
Dates (Lookup table)
Customer Accounts (Lookup table)
Sales records a customers id, sales amount, with an invoice date.
Credit memos records a customers id, return amount, and a paid date.
I want to attach both the Sales and Credit Memos table to both the Dates and Accounts tables (which are lookup tables), but when I try to create the relationships, its telling me it creates ambiguity between the dates and accounts tables.
What I want:
Sales Table connected to dates table on invoice date, and connected to Customer Accounts table on Account_ID
Credit Memos table connected to dates table on paid date, and connected to customer accounts table on Account_ID
How do I do this properly?
