I have a basic database for a shop with product, sales and customer tables. I need to generate an invoice for each customer every 3 months to show the customers total expenditure. What I'm not sure about is the way to go about this. Should I have an invoice table?

Any help or tips greatly appreciated.

2012-04-05 21:03
by Will

How about creating a view based on a select statement using those tables. Then just refer to the view. You can also create a stored procedure or function that accepts an input like customer ID. I'd go with the view route personally.

2012-04-05 21:11
by Markasourus

Hi, thanks for the reply. Id like to go the procedure or function route. What does this entail - Will 2012-04-05 21:18

Yes

What invoices you've generated is valuable information that you don't want to lose. How else do you know who's paid which invoice?

You want to be able to say exactly what invoices you've sent out, the value of those invoices and exactly what items they correspond to.

Personally not only would I have an invoice table but an invoice items table as well.

2012-04-05 21:22
by Ben

Ahh thats a good point, so I would neeed an invoice table with records created from relevant columns in other tables - Will 2012-04-05 21:29

@Will, correct. An invoice is static in time. Either it get's paid or not. However, you need to know the amount of the invoice and what items were included in order to be able to create the next one or determine what items were paid for in this one - Ben 2012-04-05 21:32

Mmm, I cant get my head around how this works. Do I have an invoice table already created? Or do I create an invoice table as an output from a select statement? Bearing in mind the invoice need to be generated every 3 months - Will 2012-04-05 21:43

Yes, you have an invoice table already created. It has a separate unique identifier - e.g. invoice_id. Each time you generate an invoice you then insert into this table all the relevant information; id, invoicee, amount, no of items, invoice address... etc. Later if you want to find out what invoices have been sent ( or paid ) it's a very simple query - Ben 2012-04-05 21:59

Thanks for your help :) I will crack on with that and be back with more questions if I get stuck - Will 2012-04-05 22:18

Oracle PL/SQL - Generate Invoice

Yes