Here are the requirements: There will be a PO, a PO will either have catalog items or items that are not in the catalog and entered by the user. There can be multiple invoices for each PO. Each invoice item must reference the PO catalog item line or the user defined line. The problem being is that I did this type of structure but found no real way other than having about 8 tables to represent this type of data. FYI: The PO's also go back to appropriation requests that have catalog items and user defined items. But I mainly need to represent the PO->Invoice relationship and the AR->PO relationship should go just about the same way. Tables: PurchaseOrders - All the purchase orders PurchaseOrderItems - A line item of the purchase order PurchaseOrderItemDetailsC - The catalog items selected for a PO line item PurchaseOrderItemDetailsU - The user defined items for a PO line item Invoices - All the invoices InvoiceItems - A line item for an invoice InvoiceItemDetailsPOC - A line item that references a purchase order from the catalog InvoiceItemDetailsPOU - A line item that references a purchase order item that is user defined
Is there any way to shrink this down? Main reason there are 3 other tables exactly like this that represent a service request ( the initial catalog items)->AR->PO->Invoice->Inventory. Also, there are some PO's that are without an AR and some invoices without a PO. This causes even more Invoice tables. InvoiceItemsNoPOC - Items that are in the catalog InvoiceItemsNoPOU - Items that are not in the catalog and become user defined.
This concept must go from the PO->Invoice->Inventory level... there are some PO's that are not related to an AR and some invoices that are not realated to a PO and some inventory items that are not related to an invoice or PO... I did a model of all this data each section had about 6-9 tables and I ended up with about 50 tables. There has to be an easier way... :-( Any help would be most appreciative.Regards,Adam
Designing a PO/Invoice system.
Posted - 2001-03-27 : 11:58:11