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.
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.
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.