Hi,
I'm creating an application that needs to handle customer orders and financial transactions (invoices, credit notes, refunds, payments), and I'm wondering if there is a recommended database structure for achieving this?
For example, are invoices, credit notes, payments, and refunds stored in the same table, separate tables, or two tables (one for debits and one for credits)? What relationships need to be in place to track payment allocation history? Is an invoice linked to an order or just a customer?
Any help on this is appreciated.
Sterry
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
One or more invoices should always be linked to one or more orders, which should always be linked to a customer. The three kind of go together when it comes to accounting. In fact, a financial transaction (like invoices, credit notes, payments, and refunds) should not exist without a customer or some entity independent of the company books.
I personally would set it up by using Accounts Payable, Accounts Receivable and the General Ledger type model used by simple accrual-based accounting systems.
The General Ledger is actually a catch-all term for your Cash account and the Accounts Payable and Accounts Receivable accounts. Here's a nice article on that:
http://www.businesstown.co
Link the invoice table to a batch table with the batch number as a foreign key.
Accounts Payable is for the money the company owes for products or services it has received but has not yet paid for. Payments for items such as the company's office equipment go here.
Accounts Receivable is for the money the company is owed for products or services rendered but which are not yet paid for. This is where the customer invoices would go along with the credit memos for customers.
Nice article on payables :
http://www.bizmanualz.com/
Nice article on receivables : http://www.bizmanualz.com/
http://www.bizmanualz.com/
These articles are more to give you an idea of what A/R and A/P staff deal with on a day-to-day basis.
Here's a good book on the G/L: http://adamsmedia.stores.y
Hope this helps ...
Business Accounts
Answer for Membership
by: GLComputingPosted on 2007-06-12 at 07:15:15ID: 19266246
Possibly the Contact Management Zone is not the best place for this question?
But just wondering.... why create something from scratch rather that an off-the-shelf accounts application?