Re: Conditional Relationships ?

Henry,

  Thanks for the followup. I'm always interested to find out in what ways real business rules are more complicated than I imagine, and it happens often, since I haven't spent much time in the business world myself.

SK

Henry Craven wrote:

>Thanks for the response Steve.
>
>The "Real World" reality of the business rules are a little more complicated
>and simplest to say that the invoice totals vary depending upon whether
>they are based on the Order, or sum of the Order_Items. Values.
>
>Needless to say, I have no chance of changing the corporate Method
>to suite my "Ideal" data model.
>
>Erland Sommarskog in comp.databases.ms-sqlserver
>
>seems to hav hit on the simplest solution Allow nulls on the FKs
>and appky a conditional ( either / or ) Rule
>
>( got caught myself having to multi-post instead of cross post
>- It happens )
>
>Thanks again
>H.
>
>
>"Steve Kass" <skass_at_drew.edu> wrote in message
>news:aum48k$5e$1_at_slb6.atl.mindspring.net...
>
>
>>Henry,
>>
>> Maybe I'm missing something, but I would think if an
>>invoice can cover just part of an order, then you just want to
>>relate the invoices to the Order_Items table (which presumably
>>is in turn related to the Orders table).
>>
>> You should be able to write procedures or triggers to make
>>it easy to associate an invoice with an entire order (so the user
>>doesn't have to specify the individual items), and you can also
>>see whether an order is fully invoiced by searching for
>>order items for that order that are not invoiced.
>>
>> I suspect I'm missing some reason why relating an invoice
>>to all the items of an order (which I'm suggesting for a full-order
>>invoice) is not the same as relating an invoice directly to an order,
>>so let me know.
>>
>>[Apologies for sending separate replies to the two groups - for
>>some unknown reason Mozilla just told me I can't send to two
>>news servers at once, though it's hard to believe I've never done
>>that successfully before...]
>>
>>Steve Kass
>>Drew University
>>
>>
>>Henry Craven wrote:
>>
>>
>>
>>>I'm curious as to how one would go about setting up
>>>and enforcing a Conditional Relationship.
>>>
>>>An example would be :
>>>
>>>Orders Table
>>>Order_Items table
>>>Invoices Table
>>>Invoices_Intersect Table
>>>
>>>The Invoices_Intersect has Foreign Keys for
>>>Invoices
>>>Orders
>>>Order_Items
>>>
>>>The relationship needs to be enforced so that the Invoice
>>>relates to EITHER the Order ( as a Whole ) 1 -2- 1
>>>OR the Order_Items ( Order part Shipped ) n -2- 1
>>>BUT NOT both.
>>>
>>>TIA
>>>--
>>>Henry Craven
>>>
>>>
>>>
>>>---
>>>Outgoing mail is certified Virus Free.
>>>Checked by AVG anti-virus system (http://www.grisoft.com).
>>>Version: 6.0.431 / Virus Database: 242 - Release Date: 17-Dec-2002
>>>
>>>
>>>
>>>
>>>
>>>
>
>
>
>
>
>---
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.431 / Virus Database: 242 - Release Date: 17-Dec-2002
>
>
>
>
Received on Mon Dec 30 2002 - 03:36:15 CET