Accounts Payable Database - Help needed building from new.

[rm.harper]

Hi,
I'm currently working on a short project to create a database for accounts payable. The main use of this database will be collecting all details from invoices and credits from a particular supplier and print reports of invoices due etc.

It shouldn't take me too long but I wouldn't mind some advise before I jump head first :banghead:

When an order Is invoiced sometimes a partial credit is needed against it which then changes the amount due on this invoice. I would like to some how link both invoices and credits together so when a credit is issued against an invoice I can quickly enter the invoice number, add the credit to it (while also inputting the credit note information).

My thought was using the ID field as my invoice number - would this correct or should I use the autonumber function anyway?

As far as I have got:
Tables needed
Invoices
Credits
Payments.

Thanks in advance

 

[spikepl]

Just to get it straight: who is issuing invoices? You or the supplier or suppliers? How do the invoices get into your database? It is rather confusing in that if you issue invoices why is this "accounts payable" but if you receive invoices, then the invoice number is set on the outside, so how can you speak of "my invoice number" ?

Also

My thought was using the ID field as my invoice number

"ID field" of what?

 

[rm.harper]

Just to get it straight: who is issuing invoices? You or the supplier or suppliers? How do the invoices get into your database? It is rather confusing in that if you issue invoices why is this "accounts payable" but if you receive invoices, then the invoice number is set on the outside, so how can you speak of "my invoice number" ?

Also

"ID field" of what?

Apologies, didnt explain myself properly there.

Invoices are issued by one supplier. I will begin to input them as recieved into my Invoices table.

By 'my' invoice number i mean the supplier invoice number.

Each invoices entry row starts with the ID field - i just wanted to know if i should use the supplier invoice number as my ID field entry or keep it as the generic autonumer.

Many thanks,

 

[ButtonMoon]

By 'my' invoice number i mean the supplier invoice number.

Each invoices entry row starts with the ID field - i just wanted to know if i should use the supplier invoice number as my ID field entry or keep it as the generic autonumer.

I would expect the combination of the supplier's identifier and the supplier's invoice number to be the composite key used by your users to identify invoices in invoice table. It ought to have a unique index on it. Is that what you mean by "ID field entry"? I wouldn't expect it to be just one field but at least two because two different suppliers might possibly use the same invoice number (perhaps unlikely but definitely possible).

The choice of primary key is a different question - related but separate because it doesn't have to be the same as the business key on supplier and invoice number.

 

[jdraw]

@rm.harper,

In addition to the good questions and advice given by others, I would recommend you get a clear description of WHAT you are trying to do. Make sure you identify all of the things that are in scope for your proposed database.
Does this proposed database relate to, or interface with, any other database? (Finance??)
Is your proposed database really only dealing with 1 Supplier? (How are other Suppliers handled?)
What constitutes a Credit and Partial Credit?
Perhaps a little more detail would clarify the bigger picture.

If you get a draft data model (tables and relationships) on paper, you can test that model against your "business rules". With some test data and a few scenarios, you can test the model and determine if the database structure supports your needs.

This may seem like extra effort, but getting your tables and relationships designed to support your requirements is a critical part of database.

Good luck with your project.

 

[spikepl]

You can use autonumber - that makes it easier to specify related tables. You do need a composite index though, on SupplierID and SupplierInvoiceNumber, to preclude inadvertent doubles. But there is no need to create any fancy new composite IDs

How exactly do the invoices get into the db? Some poor soul has to read a PDF and retype info from it ? PDF's can be read by automation (unless they contain picture scans).

 

[JLCantara]

You don't need any composite key. You will notice, in the attached schema, that the flow of info. is simple and clear. I linked Payments to Invoices tough I know for sure that they should be linked to the Suppliers in order to deal smoothly with payments error (over/under payments).

 

[ButtonMoon]

You don't need any composite key. You will notice, in the attached schema, that the flow of info. is simple and clear

Without a uniqueness constraint on (Supplier, InvoiceNumber) your suggested schema would allow duplicate data and it would no longer be possible to identify invoices uniquely for each supplier. Maybe an invoice would get paid twice as a result (it happens more often than you think). That's why (Supplier, InvoiceNumber) should be a composite key, which it typically is in accounting systems.

 

[JLCantara]

@Button: I think your not familiar with the reading of a DB schema.

1 - It is clear that any supplier can have many Invoices: at least here, in Québec, invoice numbers of a supplier must be unique (for obvious accounting reasons).

2 - A multi-tabbed form would use the supplier auto number AND the invoice auto number (cascaded combo) to locate an invoice and enter a credit note. As I said above, payments should not be linked to invoices but to the supplier.

It so happen that it's an aspect of accounting that I know very well.

P.S. - Since it is stupid to say something and do the converse, here is what I think is a good solution to the present issue.

 

[ButtonMoon]

I am extremely familiar with reading, and designing, database schemas.

invoice numbers of a supplier must be unique

Exactly, and that's why it makes sense to have a uniqueness constraint on the composite of Supplier and Invoice Number, i.e. a composite key. Failing to do that would mean the database design would not be an accurate representation of reality, would compromise data integrity and would violate 3rd Normal Form.

 

[JLCantara]

@Button: you refuse to admit that you are WRONG. Since legally, for any given supplier, invoice number MUST be unique where is the problem?

 

[ButtonMoon]

@Button: you refuse to admit that you are WRONG. Since legally, for any given supplier, invoice number MUST be unique where is the problem?

Of course suppliers issue unique invoice numbers, but people still sometimes make mistakes when keying them into computer systems. The point of enforcing uniqueness is to help ensure data integrity by preventing the database from getting in an incorrect state in the first place. Every accounting system that I have worked with prevents duplicate invoice numbers from being entered and often that is enforced in the database.

Furthermore, if a set of columns is supposed to be unique then queries on those columns will be more efficient with a unique index than without one. What do you have against unique indexes and 3NF? What is it you believe I have said that is "wrong" I wonder?

 

[JLCantara]

I don't want to get into that kind of s... Case closed.

 

[ButtonMoon]

That's OK by me. You asked what the problem was and I answered. Maybe this discussion will help someone.

 

[CJ_London]

As I said above, payments should not be linked to invoices but to the supplier.

I agree because you can have unallocated payments, but there should be a separate allocation table (like a many to many relationship) with paymentPK on one side and invoicePK(s) on the other plus an amount.

There should/could also be a similar table to link this allocation table to invoice items where payment is only made against certain items that have been approved for payment, leaving others unpaid - often generated from a payments approval process.

Both these tables would normally come from a payment approval process so the actual payment process would be working off these rather than directly off the supplier/invoice tables.

 

[JLCantara]

@CJ: I don't understand your need for allocation. I learned the handling of payments in the early 80's at Graybec, a subsidiary of Graymont. At that time, we had a package linking payments to invoices. The chief accountant explained to me that a customer is actually refunding a debt not paying an invoice. Think of your credit card: can you specify what you are paying for? Of course, a given invoice can be disputed (and mark as such in the DB) but it changes nothing: your are paying because you have a debt with this company.

 

[CJ_London]

I

don't understand your need for allocation

Among another things I am an accountant and have built a number of accounting systems over the years.

On the sales side, if you sell to a customer say 5 times over a month at £100 a time - so you have 5 separate invoices. The customer then sends you a cheque for £400.

You need to allocate that payment to the invoices so you know which one has not been paid - why? for two reasons - sending of statements and because if the customer continues not to pay and you need to take them to court - and to do that you have to be specific about which invoices have not been paid.

Much the same goes for your suppliers - you need to be specific about which invoices you are paying and which you are disputing

 

[JLCantara]

@CJ: You see when I wrote my reply, I was referring to things that occurred 30 years ago. After a good night of sleep, I remembered how we actually handled this issue - at the time we were selling HP250 (a 64K micro with 1MB floppies). HP is actually the true pioneer in desktop and micro computer development.

Invoices had status flag plus a payment ID and the amount to handle the underpayment case; overpayment (in happened!!!) where generating a credit note with proper info. The practice of check box to indicate invoices paid was abandoned.
One think leading to another, at the Jolichaux site (where we modernized our accounting system) I made a form that was printing the labels for account statements; all were happy (for the time it saved) except one person: the postmaster. There were approx. 250 letters and I forgot to sort them according to ZIP!!!!

 

[rm.harper]

Thanks for all the replies guys! I will read through them all shortly and reply.

 

[rm.harper]

Just want to say thank you to everyone who posted a reply.

But this topic will be taken no further as im not staying on a forum full of stuck up access sadbags with more time to argue about a topic that is not up for discussion than helping with a basic issue mentioned!

From another thread*

Thank you again!

Bye.