Best way to store invoices,Credit Notes??

[KenBZim]

Quotations, Invoices, credit notes, Goods received vouchers, Purchase Orders
How should these be stored within a database?
My thought was to have a table that contains all the general information for all these documents i.e.

Document_Number
Document_Type i.e. 1=Invoice, 2=Credit Note, 3=Goods Received Voucher etc.
Accounts code
Account Name
Address
Document date
Order Number
Etc. etc.

A second Table would contain All the Document Lines i.e.

Document _Number – (Ties up with The Table as above)
Document_Type (as above [Invoice, Credit Note, GRV etc])
Quantity – (This may be positive or negative depending upon the type of document)
Product Code
Product Description
Price
Discount %
VAT / Tax Rate
Etc. etc

Is this the correct way of doing this or should each document type have its own main table and each document type have its own document lines table?????
My concern with using a single table for each is that the table would be fairly large after a period of time yet look up would be really simple?

[Ecniv]

Depends how normalised you go and what you are storing where...

Presumabley

Document_Number
Document_Type i.e. 1=Invoice, 2=Credit Note, 3=Goods Received Voucher etc.
Accounts code
Account Name
Address
Document date
Order Number

is the top level info...
Wouldn't you store customers address/details etc in their own table and use an id link?

I assume doc_number is the primary key?

the second table

Document _Number – (Ties up with The Table as above)
Document_Type (as above [Invoice, Credit Note, GRV etc])
Quantity – (This may be positive or negative depending upon the type of document)
Product Code
Product Description
Price
Discount %
VAT / Tax Rate
Etc. etc

Is like a snap shot of prices along with the doc_id to tie it to the top level
I assume you have another primary key in there to identify each line?
Also, Do you need the doc type again?
Products should be in their own table (code / description) and a foreign key used (productid ?)

I hope your field names aren't having spaces and other characters in - might make more problems later on.

Szlamany has put up posts in the forums several times with table structures for this type of db, you may be able to find one or two of those for reference?

[Hack]

I hope your field names aren't having spaces and other characters in - might make more problems later on.

I would go one step further than this and say if you have spaces in your field names, it will cause problems later on.

If you do, connect the words with an underscore so they are all one, continous string, i.e. - Street Address will server you much better by being named Street_Address.

[KenBZim]

Thanks Guys
Please ignore the field names they do not even exist as yet, I always use One Word field names anyway.
Ecniv
I thought that it better to store the customers details in the top level document table rather than refering to a customers table in case in a given instance the delivery address etc. may change for a single document.

doc_Number would be the primary key yes
Yes there would be another primary key in there to identify each line

Products should be in their own table (code / description) and a foreign key used (productid ?) - the reason once again is if the user chooses a product from the products table but in this one instance wishes to change the description of this product then this one off change should not affect the product table.

In your opinion is it necessary to have different tables for the different types of document???

[Hack]

I would have a table for document types. Example:

TypeNo Doc_Type
01 Credit Document
02 Invoice Document
03 GRV
04 Etc

Then, when actually stored in your data table, simply store the type code.

[GaryMazzone]

I personnall hate duplication of data. I would build the Customer table and maybe (since you say they could have more then one address) a second table that would hold address information for each customer name. Then each customer can have multiple addresses that could be used.

[KenBZim]

Thanks Hack

With reference to the actual lines in the document ie Quantity,Product Description,Price, Discount etc. do you think that these should all be stored in a single table irrespective of the document type or do you think that Invoices should be in an Invoice_Table, Credit notes in a Credit_Notes Table
etc.

[Hack]

It would depend on their relationship.

[Al42]

If you have a General Ledger table, you have all transactions in that table, and a Trans_Type field. If you calculate the general ledger you use a Debit table, a Credit table, etc. Either way is valid. One way results in some code being faster, the other way results in other code being faster.

[szlamany]

I see three actual important figures that are related to accounting.

Those would be debits, credits and encumbrances.

All these figures go into a common LEDGER table.

Pay an invoice and you fill the DEBIT AMT.

Post a receipt - fill the CREDIT AMT.

Write a purchase order - 0 for debit and credit - put the amount in encumbrance.

Pay an invoice against a purchase order - fill the DEBIT AMT and the ENCUMBRANCE amount.

If at any time you want to derive the current encumbrance against an account - it's a simple SUM(ENCUMBRANCE) on those rows.

And the DEBIT and CREDIT amounts - also simple SUM()'s.

All fields that are common should go into the LEDGER table. Even VENDOR NUMBER - which at times isn't part of a transaction - goes into this table. Left as a 0 for rows where it does not matter.

If the row is a "PO creation row" then fill a field with the PK of the PO data that you write to a PO table.

If the row is an invoice row then fill a field with the PK of the INVOICE data that you write to an INVOICE table.

Same goes for other documents that you need to have other tables for.

But bottom line is consider the accounting first.

[KenBZim]

Thanks Szlamany - Very helpful information