List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:February 14 2006 1:31am
Subject:Re: General DB Design Question - How to avoid redundancy in table
relationships
View as plain text  
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
Thread
General DB Design Question - How to avoid redundancy in table relationshipsScott Klarenbach14 Feb
  • Re: General DB Design Question - How to avoid redundancy in tablerelationshipsPeter Brawley14 Feb
  • Re: General DB Design Question - How to avoid redundancy in tablerelationshipsBob Gailer15 Feb