Design db schema for one off and recurring payments

Public Clarification Board

• 

  hoyleg

  • 4 years ago

  4
  As for the removal in 1, mark the subscriptions as disabled until the end of the billing period at which point they are removed but leave them on the proforma until it is invoiced.
  
  5
  I'm not exactly sure what this means (an illustration may help) but I've added the Quantity and PriceOverride fields to the subscription to represent the fact that your code might be able to use these or similar fields to carry out specific ad hoc circumstances that are not easily described by the structured data.

  • 4 years ago
• 

  hoyleg

  • 4 years ago

  1
  Insert subscription items for first three rows
  Create proforma to calculate projected price until the next payment period.
  Insert another subscription item with today as the DateAdded
  Proforma can be recalculated at this point using the appropriate date combinations.
  For the removal of a place, the subscription is marked as disabled or simply removed from the table and the ProformaItem left in place, possibly needs a note adding on the ProformaItem to represent that this is a pro rata item that will cease to exist in the next invoice. Once the billing period is over it can be removed from the subscriptions table.
  
  2,3
  The refund would be stored as a negative amount in the Payments table with an appropriate description
  
  Not sure the relevance of the "7 days" in 3.

  • 4 years ago
• 

  hoyleg

  • 4 years ago

  General cases (2):
  Proformas are removed as soon as invoices are generated if products and places need to be deleted at any time, however that cannot happen whilst they are active within a proforma.
  Payments can be positive or negative within the system to represent both payments made by the customer and refunds sent to the customer. It isn't a double entry system as so would not add up to zero per customer, however this could be achieved if necessary.
  The link from Payment to Invoice is optional as not all payments are necesarily tied to a specific invoice.
  This whole thing could probably be done without the proforma altogether and just work from the previous invoice date to the same day next month as the billing period but this way seems a little cleaner.
  Invoices could be linked to proformas and invoiceitems to products etc. but this would introduce links that could not be broken so it rather depends on specific requirements about the lifespan of rows.

  • 4 years ago
• 

  hoyleg

  • 4 years ago

  General cases:
  Users place/product combinations added to Subscriptions
  On creating a new subscription a new proforma is created to represent the pending invoice. Using the subscription "created", product pricing details and the proforma's opening and invoice dates a projected price can be calculated at any point or even stored on the proforma at parent or child level if required.
  At invoice date the invoice is generated and a new proforma created to represent the next month.
  Fields are copied from subscription to proforma to invoice to represent the fact that the subscription may change once the invoice has been paid.
  Invoices do not contain any reference to the proformas, products and places to enable the data within the system to be changed without being tied up forever, e.g. products can be removed without causing reference issues on old invoices. These links can be introduced but not enforced if necessary, or products and places could be soft deleted and have a "deleted" flag.

  • 4 years ago