Re: supersunday modeling question

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.

REgards,
Chas. Received on Mon Feb 05 2007 - 23:39:40 CET