Receipt/invoice database

[eyalco]

I'm building a receipt/invoice database - I've started with the invoice first, then the receipt.
I have invoice = parents and sons + receipt = parents and sons tables + a customer table.
I want to issue 1 receipt with 12 payments (1 check for each month of the year) and then, everytime a check comes to delivery, the invoice is printed out with the receipt number.

What would be the relation between the receipt/invoice tables and how do I make the relevant receipt number for each invoice please?

Thanks.

[missinglinq]

Perhaps my brain is addled (although I didn't celebrate last nite!) but

"I have invoice = parents and sons + receipt = parents and sons tables + a customer table"

makes absolutely no sense to me! I think you're really going to have to do a better job of describing what you already have in place. Posting of meta data info should be something on the order of:

Table1
FirstFieldName (DataType)
SecondFieldName (DataType)
ThirdFieldName (DataType)

Table2
FirstFieldName (DataType)
SecondFieldName (DataType)
ThirdFieldName (DataType)

Linq ;0)>

[NeoPa]

Like general database work, the idea is that the more effort you put into asking a question clearly, the less problems we all have over all.
The parallel is so close to database design. Do it properly and the whole project benefits. Do it sloppily and you spend most of your energy clearing up the resultant problems.
I don't want to be overly harsh, but as Linq says, your question could do with a little more thought and careful preparation.

[eyalco]

Thanks friends, you're right.
Here's my basic layout of the database :

TBL_CUSTOMER :
CustomerID AutoNumber primary key
Surname Text
FirstName Text
Address Text
City Text
Zip Text

Invoice table:
InvoiceID AutoNumber primary key
CustomerID Number who is to pay this invoice
InvoiceDate Date/Time when the invoice was sent
Descrip Text (or memo) what this is for
AmountEx Currency amount of this invoice, without tax.
TaxRate Number (Double) Percent tax rate to add.

Invoice_sons_tb l:
InvoiceID AutoNumber primary key
ID_Item Number who is to pay this invoice
Item_descriptio n text what this is for
price_per_unit Number what each unit worth
Discount Number
Quantity Number
Sum Number

TBL_SUB_RECEIPT :
InvoiceID Number
Payment Text
Bank Number
Branch Number
ACC Text
Payment_Date date

Receipt table:
ReceiptID AutoNumber primary key
CustomerID Number who paid this amount
ReceiptDate Date/Time when received.
Amount Currency amount received.
Comment Memo description of what for.

I joined the receipt table to the invoice table in a one to many connection, but think it might be better to join both to customer perhaps?
Thanks guys.

[NeoPa]

That's a good first step eyalco, but as Linq says in his post (#2), your basic question is also phrased in such a way as to be very hard to understand (I didn't manage to work out what you were saying).
Perhaps you could try rewriting it carefully (remember punctuation can be your friend - especially where there is ambiguity).
I get that you want to know how best to design your table structure (a good question by the way - shows you're thinking along the right lines). What I don't get is the scenario you have to work within.

[eyalco]

Thanks.
As you said, I want to know what w'd be the best way to join the relations between the tables - will it be throug receipt ==> invoice in a 1 to many relation or is it better to join both to customer table? or any other suggestion you might have?

The 2nd thing I mentioned was that each receipt has 12 invoices (1 for every month of the year), I want to relate each monthly invoice to the relevant receipt so I can see for each invoice to which receipt it "belongs" and on the other hand, when looking at on going receipt, I can see how many invoices are related to.

I hope I'm clearer - please direct me if not.
Appreciate your help.

[NeoPa]

I'm afraid you don't seem to have grasped the problem here. Let's try again.

First you need to describe the overview of your project clearly. Don't use any terms that have not already been defined and made clear. There's nothing more frustrating than being told you needn't worry about the urgles as they're simply offshoots of the oojimaflips.

Explain how all the parts of the project fit together. For this purpose you can ignore any parts of the project that don't pertain to your particular problem or question. We're mainly looking to pass the question on from one party to another via a web page.

Avoid making unfounded assumptions. Because you know how receipts relate to invoices in your system, don't assume that they are connected that way universally, or even that if they were, someone would know that without being told.

Pay particular attention to explaining how the particular tables that you're asking about work together logically.

If you can explain all this clearly then we can start looking at your issue and recommending the best way forward :)

[eyalco]

Well, I'll explain the project, maybe it will clear things up :
It's a kindergarten where parents come once a year and pay with 12 checks, 1 for each month. Upon payment, they receive a receipt with their 12 checks listed. Every time when a check is due, we issue an invoice.

That's it.
Hope you can help.
Thabks.

[NeoPa]

So, if I understand you correctly :
An invoice is issued once a year (when all twelve cheques are due).
An invoice consists of twelve separate items and payment for all twelve are expected in one go. Hence this is like a single item.
Receipts are issued for each item, but the twelve separate months are included on the one recipt. Making it similar to the invoice.

What would be the relation between the receipt/invoice tables and how do I make the relevant receipt number for each invoice please?

1. In this case you need would have a one for one relationship between Invoice and Receipt tables. Assuming the invoice is entered first, the receipt should have a foreign key field pointing to the matching invoice record.
2. No idea. There is no information supplied for this.

[eyalco]

Few clarifications please :

So, if I understand you correctly :
An invoice is issued once a year (when all twelve cheques are due).

It's the other way around : A receipt is isuued once a year - with 12 utems, each item is a chexk(payment) details.

An invoice consists of twelve separate items and payment for all twelve are expected in one go. Hence this is like a single item.

A receipt consists of twelve separate items. Payment is seperated monthly - each month = 1 payment.

Receipts are issued for each item, but the twelve separate months are included on the one recipt

Receipts are issued for 12 items at once, only once a year.
Each receipt consists of 12 monthly payments or actualy of 12 invoices (a check/payment becomes an invoice every month when it's due.

Does that seem different then what u mentioned? will it change your answer?
Thanks.