These are the tables in question: RFQ (Request for Quote) Part Inventory
Inventory items ALWAYS have a partID. RFQ items ALWAYS have a partID. However, sometimes, RFQ items have an inventoryID as well. Now, we have a redundancy problem. Because, in those instances when the RFQ has an inventoryID, the partID should be derived from the inventoryID. If there is no inventoryID, then the partID needs to be stored directly in the RFQ table. We don't want to have both the inventoryID and the partID in the RFQ table, because it opens up data integrity issues. ie, what if the RFQ item shows inventoryID 2, and partID 1...but inventoryID 2 is associated to partID 2. Now which partID is correct? They can't both be right. I'm sure this type of problem is run up against all the time, and I'm wondering what the best practice methodology is from experienced DBA's. This was a simple example; however, we are running into the problem system wide. For example, a quote table has an OPTIONAL RFQ ID, and a mandatory contactID. The RFQ table has a mandatory contactID. If the quote table has an RFQID, we want to derive the contactID from the RFQID. If the quote has NO RFQID, then we need to store the contactID directly in the quote table. In those instances where there IS an RFQID in the quote table, we end up storing the contactID twice. Once in the quote table, and once in the association between the RFQ/Contact table. Same problem as above: integrity and poor overall design. Thanks for your advice.
