I have a series of tables. I have a table of Invoices, a table of Purchase Orders, and a table of Items.
Both the Invoices table and the Purchase Orders table have a field called PurchaseOrderID so they share a key. They also both have a field called ItemID, which I do not want them to share. My Items table also has a field called ItemID that I want shared with the other two tables.
I want my Purchase Orders and Invoices tables to share PurchaseOrderID together but not ItemID
I want my Purchase Orders and Invoices tables to share their ItemID field with the Items table but not each other.
I'm going to have other tables that meet this scenario, so what is the best approach to this?
If the invoice is always for the same item as the purchase order, just remove the item field from the invoice. I don't think that's your situation, though. I gather that you might invoice a different item than the purchase order specifies? Like you might ship them a different item as a substitute?
You could duplicate the item table with different field names for everything (in this case, something like "Purchase Order Item" and "Invoice Item" as prefixes). This is very common in QlikView.
Another option that normally applies is to create a linkage table linking the purchase order, invoice and item tables. However, since the purchase order and invoice also connect, that would cause a loop, which is not allowed. There's probably a way around this, but it's not jumping out at me.
