The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Invoice / Database Design

accounting/billing datatabse - i cant figure out if i should have a purchase transaction table and a sales transaction table ie purchase tracks money out and sales tracks money in

or should it just be a single table for both with a single amount coulmn where money in = 10.00 and money out = -10.00

whats the best way to track transactions per invoice (and per item on invoice  ie assaciat a transaction both credit and debit with with an item on an invoice)
Andre Gel Send private email
Wednesday, October 25, 2006
I think that you are vastly underestimating the future requirements of your system. Take a look at something like Microsoft Small Business Accounting and you will see somewhere on the order of 200 tables (if I remember correctly). I write Point of Sale software and a typical transaction impacts at least 20 tables. So thinking in terms of 1 table vs. 2 is probably not going to get you very far. You probably need a better grasp on the domain model before making a decision.

But you could technically go either way and it won't matter. But I personally wouldn't assume that a positive/negative amount is going to tell the whole story. You should expect to have a header/detail type relationship (an invoice can have multiple line items with potentially positive and negative amounts). So the best bet is to use an amount field with an associated line item type field (sale, return, pay out, payment on account, etc.) that tells you what the line item is for. You would also typically have a foreign key back to a header table that contains general information about the invoice itself.

Good luck.
Turtle Rustler
Wednesday, October 25, 2006
Thanks Turtle -  that helps heaps, Your probably right, i 1-2 tables wont cut it if i want decent reporting
Andre Gel Send private email
Wednesday, October 25, 2006
More, you'll need to manage other documents like credit and debt notes. These may be "value only" or derive from returns, so the same as a reversed order.

This kind of sw, usually, features a central master/detail table group and countless lookup tables.
Sevenoaks Send private email
Thursday, October 26, 2006
So if i am selling product A (PA), service A(SA) and Gift Voucher A(GA)

I would have on my invoice Item 1 = PA, Item 2 = SA, and Item 3 = GA.

i dont know where to record this purchase transaction, in a table called transactions, and if so how is that assoaiated with the 3(maby more) different types of items

or do i only record payments made on the invoice in transactions and then have each item type track sales via a many to many relationship

should i use a table called items and associate them with invoices/transactions and products?
Frank R
Thursday, October 26, 2006
One possible (simplified) solution:

Table: Customer.
Identifies customer by customer_id.
Contains: Name, address (or one or more addr_id to separate address table).

Table: Product.
Identifies product by product_id.
Includes name, description, price.

Table: Invoice.
Identifies invoice by invoice_id (internal) and invoice number (what the customer sees).
Contains fields: issue_date, customer_id, due_date, status (open|past-due|paid|cancelled), payment_terms_id.

Table: InvoiceItem.
Contains fields: invoice_id, product_id, quantity, price.

Table: PaymentTerms.
Identifies payment terms by payment_terms_id.
Contains: label, due_days_1, late_penalty_1, due_days_2, late_penalty2, due_date_3, late_penalty_3, collection_days.

PaymentTerms example: Net-30 with 1.5%/month penalty, send to collection after 120 days ---> "Net30/1.5%", 30, 0.015, 60, 0.015, 90, 0.015, 120

Table: Payment.
Identifies payment by payment_id.
Includes: customer_id, date, amount, optional invoice_id.

Invoice total amount = select sum(II.quantity*II.price) from InvoiceItem II where II.invoice_id = :invoice_id

Invoice payments to date = select sum(P.amount) from Payment P where P.invoice_id = :invoice_id

To get customer outstanding balance, sum the invoice total amounts for the customer and subtract the sum of the customer's payments.

An overnight batch process examines each open invoices and determines whether to mark it past due. If past due, the process changes its status and adds a penalty fee "product" (if specified).
Samuel Reynolds Send private email
Thursday, October 26, 2006
I think Samuel Reynold's tables designs are just about right for your needs. However :-

(1) You will need 2 separate tables, one for your own company's sales invoices (call it the SalesInvoice table) and then there is your supplier's (purchase) invoices (call it the PurchaseInvoice table)

(2) You might want to also create separate additional tables for DebitNotes and CreditNotes for both sales and purchases.

(3) For payments to your suppliers for purchases you will need (as Samuel said), a Payment table. This records money out.

(3) For collections (money in) received from your customers for your sales you will need a Collection table.

If your solution is really, really simple as you originally suggested you just wanted to record money in and money out, 1 table would suffice - create a special column to record whether it's money in or out (eg. 'IN' or 1 for money in, 'OUT' or 0 for money out). I would really call this table the CashBook table.
Ezani Send private email
Tuesday, October 31, 2006

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz