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
>
Attachment: [text/html]
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
