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 Mon Feb 05 2007 - 17:22:15 CST
