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.

[NeoPa]

Let me explain what is going on here and why I'm not going to spend any more of my time on this thread - or any other where you show the same attitude to requesting free help.
Post #7 (NeoPa)
(translation)
I've tried to decipher what you've posted but it doesn't make sense. This is because you can't be bothered to formulate a question properly or clearly.
Please try again and pay particular attention to various points - including that it's written in proper English so there are no ambiguities.

Post #8 (eyalco)
(clearly ignoring almost everything I say AGAIN)
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.

Ambiguous. As short as possible to save YOU time and effort (which of course adds both on to my plate - thank you).
It's not even checked before posting. I ask for care and you sign off "thabks". That doesn't say any care at all to me.

Post #9 (NeoPa)
Trying to make sense out of what doesn't naturally make sense.
I offer up my interpretation of your project, knowing it's unlikely to be 100% accurate as post #8 doesn't hold together well.
I offer an answer to one of your questions from the information already posted.

Post #10 (eyalco)

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

Note three typos included in a single sentence even now.
This directly contradicts "It's a kindergarten where parents come once a year and pay with 12 checks".
I'm sure you meant this to be different from what you posted - but you couldn't be bothered to make it say what you meant. I've rarely seen such a simple issue so clouded by poor posting in all my time here at TSDN.

Frankly, after being requested (and told by a mod) to post your question more clearly four times, you still cannot be bothered to formulate your question clearly, or even check your post for typos before you post it (or even read it to yourself to check after posting and fix them).
I'm thoroughly shocked that you show such blatant disregard for the time and efforts that are put in by someone trying to offer you free assistance.

[eyalco]

I really am sorry if I offended you or anyone in this forum which I admire greatly.
Regarding my spell or grammar, english is not my native tongue so bad grammar or spelling isn't disrespect.

I do appreciate your time trying to help and my last post was meant to explain more of my problem and not show any kind of disrespect.

Again, my apologies

[NeoPa]

Eyalco,

I appreciate your last response, and I will certainly bear in mind for future reference that English is not your native language. It may be worth updating your profile to show where you come from (country). This is not required (you don't HAVE to) but can sometimes help the moderators and experts to make allowance for possible communication problems (I certainly checked there first).

With regards to your question, I will look at it again for you when I can get some time free.

[NeoPa]

An Invoice then is a logical child of the receipt, but the Receipt (presumably) must come after the Invoices.
I suggest then that each year you create a (dormant) Receipt at the start, with a link to the Customer (The Customer key should be stored as an FK (Foreign Key) in the Receipt record). Each Invoice would have a link to this Receipt record (again, Receipt key stored as an FK in Invoice record). When the Receipt is actually produced, then a flag could be changed in the Receipt record to indicate this.

PS. Sorry this has been a long time in coming - it's been a very busy week for me.

[eyalco]

Thanks for your reply.
I'll start playing with it, see where It'll take me.

Appreciate your help.