I am in the design phase of a project and I am having some trouble finding a solution that can perform queries efficiently with PostgreSQL backend
Say, there exists an item and each item has fields amount, payer, payee, proxy and a switch
imagine payer = customer, payee=vendor, proxy=some_third_party
Switch Condition: if switch is false [payer > payee = $amount] if switch is true [payer > proxy = $amount] and [proxy > payee = $amount]
For any item, proxy is always the same third_party
Considering the above condition for items, say there is a
say, has 10 items - 4 items from vendor_1 and 6 items from vendor_2
Expense Invoice or Bill
a bill is what a vendor provides for items purchased from them items from the Job are present as whole or split across one or multiple bills items from vendor can be on a single bill or split across multiple bills from the vendor Example: item_1 in Job cost 100$ - $100 can be in bill_1 or - $40 in bill_1 and $60 in bill_2
Sales Invoice or Invoice
invoice is what the third_party provides to the customer items from the Job are present as whole or split across one or multiple invoice items from vendor can be on a single invoice or split across multiple invoice Example: item_1 in Job cost 100$ - $100 can be in invoice_1 or - $20 in invoice_1, $30 in invoice_2 and $50 in invoice_3
payment is always made between payer, payee and proxy money from the payment can be split to pay invoices - thereby extension, pay items Example: if payer transfer 40$ to payee - $40 can be used to cover invoice_3 or - $20 in invoice_1 and $20 in invoice_2 In the above example, say invoice_2 has 2 items (item_1 - $10, item_2 - $20) $20 paid on invoice_2 can be spend on covering item2 or split across both items
With all the above in mind, how should these models be linked together so that I can make queries like
Show all amount some_payer has to pay to some_payee on a Job and what amount remains to be invoiced - result can be asked to further split by item
Show all amount some_payer has to pay to some_payee on an invoice, total amount available in bills associated with the items on that particular invoice - sorted by total number of bills available for the invoice
As extension of the above query, now consider payments involved in the equation. How much is paid, or sort by descending order or payment remaining , etc.
Basically run any type of combination.
What is a design solution in Django that can support this sort of querying ?
is this sort of complex querying is going to need more than an efficient model design ?
Like bringing in DataScience/BigData approach... Hadoop, ElasticSearch ?
Any input, guidance, suggestion, are welcomed.
Thank you for your patience reading through this.