The problem with storing pending invoices as ordinary invoices (with a "pending" flag/status) is that there will be hundreds of operations/reports that are only supposed to take into account posted invoices, which literally means every status except for pending. My advice from about 4 years of having to work with the back-end of an invoicing system that somebody else designed: Don't have a "pending" status on invoices. Best way to define and track invoice status? manager applies discount after technician creates invoice) before it is issued. However, a "draft" invoice must be editable by more than one person (eg. Invoices and invoice lines are by definition immutable, so tracking changes isn't sensible. How should I incorporate sales tax (or 14% VAT in SA) when storing invoice data?Įdit: Good feedback, guys. Thoughts? Edit: line items should be immutable. I can track revisions to an invoice by storing status changes along with the invoice total and the auditing user in an invoice revision table (see InvoiceRevisions above), but keeping track of an invoice line revision table feels hard to maintain.
#PRODUCT DATABASE IN SIMPLE INVOICES HOW TO#
How to keep track of invoice line item revisions? Is it meaningful to store a "Paid" status in the Invoices table if all the income related to a given job's invoices can be inferred from the Payments table? 2. Cash, Credit Card, Cheque, Bank Deposit).
Is it sensible to store an invoice's Paid or Pending status?Īll payments received for an invoice are stored in a Payments table (eg. Revision schema InvoiceRevisions Table RevisionId (int) // Primary key InvoiceId (int) // related to Invoices above InvoiceLines Table LineId (int) // Primary key Reference (nvarchar(256)) // unique natural string key with invoice number StatusId (tinyint) // Pending, Paid or Deleted Current schema Invoices Table InvoiceId (int) // Primary key
I'm designing the 2nd major iteration of a relational database for a franchise's CRM (with lots of refactoring) and I need help on the best database design practices for storing job invoices and invoice lines with a strong audit trail of any changes made to each invoice.
0 kommentar(er)
