>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 ...
partID PRIMARY KEY
inventoryID PRIMARY KEY,
partID FOREIGN KEY REFERENCES part.partID
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
conclude that it's time to sit down with the client. But perhaps we need
Scott Klarenbach wrote:
> These are the tables in question:
> RFQ (Request for Quote)
> 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