Re: General DB Design Question - How to avoid redundancy in table relationships
Scott Klarenbach wrote: 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. Since there have not been a lot of responses I decided to jump in. It sounds to me like we have real-world object behavior mixed up with the data model. Example "RFQ items ALWAYS have a partID If there is no inventoryID, then the partID needs to be stored directly in the RFQ table." Why? Who or what is enforcing this? Can we look at overall object behavior, then come up with a model that supports the behavior with no preconceptions of table structure. I assume that an RFQ item is a document (paper or eletronic). What does one look like? From your description it will always have a partID and may have an inventoryID. Who populates these fields? Why is there a redundancy in the first place? Who checks to see that the "direct" partID matches the "derived" partID? How about leaving partID and inventoryID out of the RFQ table, and adding an association table that relates a RFQ to either a partID or an inventoryID. An attribute of this table would distinguish partID from an inventoryID. Business logic would ensure that only one entry gets into this table per RFQ, and could also validate that the "direct" partID matches the "derived" partID 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. Same issue here. Remove the IDs from the quote and RFQ table and create another association table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General DB Design Question - How to avoid redundancy in table relationships
Scott, >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. It looks like the kind of problem database schemas are meant to _avoid_. >From your description it seems you have ... part ( partID PRIMARY KEY ) inventory ( inventoryID PRIMARY KEY, partID FOREIGN KEY REFERENCES part.partID ) RFQ ( rfqID PRIMARY KEY??? (I assume), partID FOREIGN KEY references part.partID, inventoryID NULL LOOKS UP inventory.inventoryID ) according to which ... (i) a RFQ item can reference a partID which is not in inventory, (ii) even if a RFQ partID is in inventory, it may show up in RFQ paired with a different invcentoryID, but (iii) if [ii] occurs, it indicates an error which is plumb crazy--if [ii] is an error, the schema should disallow it. The business rules embedded in this schema contain a contradiction. If it were my project, I'd conclude that it's time to sit down with the client. But perhaps we need more info? PB Scott Klarenbach wrote: 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. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.6/258 - Release Date: 2/13/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.6/258 - Release Date: 2/13/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
General DB Design Question - How to avoid redundancy in table relationships
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.
