I have a database that currently tracks classified ads in a newspaper for various customers. I have a 1-to-Many relationship established between in the Customers table and the Classifieds table. (That is, one customer can have several classified ads attached to their account.) Every 4 weeks, I need to perform a mass invoicing of all accounts with unpaid balances. I still need to create a payment processing system for the DB, but I feel that I need to complete the invoicing stage first.
Currently, I can generate invoices for all outstanding balances, but the problem lies in the fact that I cannot store the invoiced information. It's important that I'm able to store this information for customer service and billing concerns. I'm curious as to what the best possible way to generate and store these mass invoices would actually be? It seems to be that the invoices would have to be related to the classified ads in a 1-to-many relationship, similar to the Customers/Classified ads. It also seems to me that this task would have to be handled programtically to generate and store the invoice as well as the correct ads for each of those invoices.
If anyone has any suggestions on the best way to handle this, I would greatly appreciate the advice. Once I know how to generate the invoices, I'll be able to tackle the task of processing payments. I'm stuck in between billing for each individual ad or billing by the total for an invoice. Once I get this figured out, it should shed some light on the subject.