Re: supersunday modeling question
From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Mon, 05 Feb 2007 23:22:15 GMT
Message-ID: <H_Oxh.2947$R71.44277_at_ursa-nb00s0.nbnet.nb.ca>
>>On Feb 5, 4:51 am, "foothillbiker" <foothillbi..._at_gmail.com> wrote:
>>
>>
>>
>>
>>
>>
>>>All,
>>
>>>Suppose I have a table like this:
>>
>>>15:14:55 system_at_ta64 SQL> desc rowshambow
>>> Name Null? Type
>>> ----------------------------------------- --------
>>>----------------------------
>>> ROWSHAMBOWID NOT NULL NUMBER
>>> ROCKID NUMBER
>>> PAPERID NUMBER
>>> SCISSORSID NUMBER
>>
>>>rockid/paperid/scissorsid are all foreign keys to rocktable/
>>>papertable/
>>>scissorstable, respectively.
>>
>>>The biz requirements are that every record in the table must have
>>>exactly one (1) of these three fields populated.
>>
>>As Bob indicated, you should consider normalizing your design.
>>Meanwhile you could do something like:
>>
>>CHECK ((case when ROCKID IS NOT NULL then 1 else 0 end +
>> case when PAPERID IS NOT NULL then 1 else 0 end +
>> case when SCISSORSID IS NOT NULL then 1 else 0 end ) =
>>1)
>>
>>/Lennart
>>
>>[...]- Hide quoted text -
>>
>>- Show quoted text -
Date: Mon, 05 Feb 2007 23:22:15 GMT
Message-ID: <H_Oxh.2947$R71.44277_at_ursa-nb00s0.nbnet.nb.ca>
foothillbiker wrote:
> On Feb 5, 12:59 pm, "Lennart" <Erik.Lennart.Jons..._at_gmail.com> wrote: >
>>On Feb 5, 4:51 am, "foothillbiker" <foothillbi..._at_gmail.com> wrote:
>>
>>
>>
>>
>>
>>
>>>All,
>>
>>>Suppose I have a table like this:
>>
>>>15:14:55 system_at_ta64 SQL> desc rowshambow
>>> Name Null? Type
>>> ----------------------------------------- --------
>>>----------------------------
>>> ROWSHAMBOWID NOT NULL NUMBER
>>> ROCKID NUMBER
>>> PAPERID NUMBER
>>> SCISSORSID NUMBER
>>
>>>rockid/paperid/scissorsid are all foreign keys to rocktable/
>>>papertable/
>>>scissorstable, respectively.
>>
>>>The biz requirements are that every record in the table must have
>>>exactly one (1) of these three fields populated.
>>
>>As Bob indicated, you should consider normalizing your design.
>>Meanwhile you could do something like:
>>
>>CHECK ((case when ROCKID IS NOT NULL then 1 else 0 end +
>> case when PAPERID IS NOT NULL then 1 else 0 end +
>> case when SCISSORSID IS NOT NULL then 1 else 0 end ) =
>>1)
>>
>>/Lennart
>>
>>[...]- Hide quoted text -
>>
>>- Show quoted text -
> > > All, > > Thanks for the replies. What I'm actually trying to model is > something that you could consider similar to firewall rules. > > the rules could apply to may different "things" > - ip addrs > - ports > - protocols > - etc. > > Each of these "things" has parent records in a corresponding table. > > So I could make a separate rule definition table for each "thing" and > then a view that combines all the conditions for a given rule together > -- but that seems a bit unnatural.
Seems natural enough to me. However, I am not sure 'natural' has any meaning when dealing with abstract types and mathematics. Received on Tue Feb 06 2007 - 00:22:15 CET