relating Invoices and Receipts

[Paul James]

I'm building an Access 2k database to handle Invoices and Receipts, and I
have a question about the design that I'm planning to use.

Here are some of the design specs and business rules:

1. one Receipt can be for many Invoices.
2. one Invoice can only have one Receipt (partial payments aren't accepted).
3. sometimes payments are entered into Receipt for which no Invoice was
generated
4. both Invoices and Receipts have Details, and these are essentially the
same fields.

My question is this: can I keep the Invoice Details and Receipt Details in
the same table, where both the Invioce and Receipt tables have a one-to-many
relationship with the Detail table?

tblInvoice --> tblDetail <-- tblReceipt

Or should I keep them in two separate Detail tables?

tblInvoice --> tblInvoiceDetail
tblReceipt --> tblReceiptDetail

I can think of advantages and disadvantages to both designs, but I suppose
there could be an overriding reason why one is better than the other, which
hasn't occurred to me.

In view of the design specs above, is it better to keep the Invoice Details
and Receipt Details in the same table, or in two separate tables?

Thanks in advance,

Paul

 

[Ernie]

-----Original Message-----
I'm building an Access 2k database to handle Invoices and Receipts, and I
have a question about the design that I'm planning to use.

Here are some of the design specs and business rules:

1. one Receipt can be for many Invoices.
2. one Invoice can only have one Receipt (partial payments aren't accepted).
3. sometimes payments are entered into Receipt for which no Invoice was
generated
4. both Invoices and Receipts have Details, and these are essentially the
same fields.

My question is this: can I keep the Invoice Details and Receipt Details in
the same table, where both the Invioce and Receipt tables have a one-to-many
relationship with the Detail table?

tblInvoice --> tblDetail <-- tblReceipt

Or should I keep them in two separate Detail tables?

tblInvoice --> tblInvoiceDetail
tblReceipt --> tblReceiptDetail

I can think of advantages and disadvantages to both designs, but I suppose
there could be an overriding reason why one is better than the other, which
hasn't occurred to me.

In view of the design specs above, is it better to keep the Invoice Details
and Receipt Details in the same table, or in two separate tables?

Thanks in advance,

Paul


.
Any system which ignores the possiblity of partial

payments is doomed to failure. You will need to allow for
partial payments, especially for large invoices. A common
theme is 20% down and balance on delivery. Right there is
two receipts for one invoice.

Also, every receipt has to have an invoice. If there isn't
one, you will need to create a credit note or pre-payment
in the invoice file to match up against. If you don't, you
run the risk of losing track of such payments and making
your customers somewhat upset (upset customers don't come
back).

As such you will find the two table approach, as you
mentioned, easier to work with.

(just my opinion)

 

[Paul James]

Thanks for the suggestions, Ernie. Sounds like you're recommending some
good accounting and business practices.

However, I wasn't kidding when I said we don't accept partial payments. If
we get a partial payment, we'll send it back. I realize this probably
appears nonsensical, but we're a government agency, and we're bound by some
constraints that don't affect businesses in the private sector. I build
those rules into my databases, especially where it simplifies the design.

Also unlike a private sector business, we receive payments without always
having to send out invoices, such as with new application fees, and I can
track those payments through the Receipt and Detail tables.

That's why I though I might be able to get away with a single detail table
for both Invoices and Receipts, or

tblInvoice --> tblDetail <-- tblReceipt

instead of

tblInvoice --> tblInvoiceDetail
tblReceipt --> tblReceiptDetail

I'd appreciate any further thoughts you might have on this.

Thanks.

Paul