Re: Should I denormalize this?

No reason to denormalize. Cases have one or more defendants. So you have a table of cases and a table of defendants and a table of the relationship between the two. eg:

Case Table
  case#, case name, description, etc.
    123
    124
    125
...

Defendants table
defendant # Name, etc.

999             Arco
1000           Shell
1001            Texaco

Case_def_relationship table
case# Def#

123      999
123      1000
124      1001
125      1001
125       999

Then you have a nice relationship between cases and defendants and defendants and cases.
Much more flexible.
Jim

--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family.  Remove the negative part, keep the minus sign.  You can figure
it out.
"Steve Quezadas" <steveeq2_at_tripperjones.com> wrote in message
news:IooW9.258$Sv3.34606_at_newsread1.prod.itd.earthlink.net...


> I am looking at this problem and I don't know how to handle it other

> than de-normalizing the table. Here is a simple table:

>

> Case # Defendant

> -------- --------------

> 123 Shell

> 123 Arco

> 123 Exxon

>

>

> Here is how I need the results to be in two different scenarios:

> Scenario #1) "Bring me all case numbers that have Shell AND Arco in

> them." Here, it would output case # 123

> Scenario #2) "Bring me all case numbers that have Shell AND Union 76 in

> them." Here, it should return an empty set since Union 76 is not a

> company associated with any case.

>

> I am not sure how to handle this. I was thinking about de-normalizing teh

> table and listing one record per case and all the defendants will be

> listed in a single field separated by a token like: "Shell~Arco~Exxon" and

> then doing a SELECT Case WHERE Defendant LIKE "Arco" AND Defendant LIKE

> "Shell" or whatever, but I am wondering if there is a more elegant way.

>

> - Steve