Need help with invoicing solution
I need help designing an invoicing solution that is slightly different from the standard one.
1. The INVOICES table is in a many-to-many relationship with the PRODUCT_GROUPS table. Each invoice contains multiple product groups, and each product group can be on multiple invoices.
2. The PRODUCT_GROUPS table is in a many-to-many relationship with the PRODUCTS table. Each product group contains multiple products, and each product can be in multiple product groups.
In my mind, I envision something like the following during data entry:
A. The user visits a layout and creates a new invoice with multiple product groups on it.
B. The user then visits a second invoice which lists all of the corresponding products for the product groups on the first invoice, and selects quantities for each product.
Can anyone provide tips for creating a solution like this? Here are some specific questions I have:
Will I need one or two (or three?) join tables? (Which table occurences will I need? What will the ERD look like?)
How do I generate the second invoice (containing individual products) from the first one (containing only product groups)?