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 two different join tables?  (What will the relationship diagram look like?)

How do I generate the second invoice (containing individual products) from the first one (containing only product groups)?

 

Thank you!

Tables required to build this invoicing solutions will be -
1. Products
2. ProductGroups
3. ProductGroupProducts - join table to establist/set many-to-many relationship between products & ProductGroups
4. Invoice - parent table with invoiceId, custmerId, invoiceDate, InvoiceStatus and more similar attribute
5. InvoiceLineItems - Child table with fields -  PK_InvoiceLineItem, FK_Invoice, FK_ProductGroup, FK_Product

 

Attached is screenshot of Entity Relationship Diagram. You can table occurences of Product & ProductGroups and relate it InvoiceLineItems for functionalities.

 

You can design functional flow in different ways, on basis of if all the products of selected product groups are included in invoice or only specific products are to added from slected product group.

 

Hope this is helpful.

 

Thanks

MetaSys, thank you for your reply!

 

I think I understand your ERD, but I'm unclear of the best way to populate the InvoiceLineItems table.

 

Assume that all products associated with a given product group must appear on the invoice.  

 

Example:  

 

Let the "Red" product group correspond to three products: "Red T-Shirt", "Red Basketball", and "Red Chair".

 

Say a user creates a new invoice with a single "Red" product group item.  My interpretation of your ERD is that three records would populate to the InvoiceLineItems table.  But how would I accomplish this?  I'm thinking of an Invoice layout with a portal to InvoiceLineItems.  Wouldn't the user need to select the three individual products rather than just one product group?

The invoivelineitems can be populated in following way.

 

On invoice layout, place a portal of invoicelineItems. On top right of portal provide a 'add items' button. On click of this button, in popup window show either a drop-down list of product groups (if number of product groups is less) or show a list view of product groups. On selection of product groups, using the ProductGroupsProduct join table, get the related products of selected ProductGroup and populate (add those many records to) InvoiceLineitems - set the Fk_productgroup, FK_ProductId fields of invoicelineitems. And this way user can add multiple productgroups to invoice.

Thank you for your help, MetaSys.  I will try what you suggest.