0

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

Job

  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

   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.

QUESTION:

What is a design solution in Django that can support this sort of querying ?

Or

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.