Re: Normalizing vs de-normalizing

An easy solution would be to use an intersect i.e.
select casenumber from defendants where defendant = 'UNION76' intersect
select casenumber from defendants where defendant in ('Arco','Shell','Exxon');

This would only returns casenumbers which appeared in the resultset of both queries. Also, assuming you have an index on the defendant column the performance should be good.

"Steve Quezadas" <steveeq2_at_tripperjones.com> wrote in message news:xbRU9.9206$Dq.949361_at_newsread2.prod.itd.earthlink.net...
> I have a database with a [Cases] table and a [Defendants] table.
> There are usually about 40 defendants or so for every case. Here's
> the deal though: Sometimes I need to pull up a query that says
> "show me all casenumbers where the defendant can be (Arco or Shell or
> Exxon) AND the casenumber has to have defendant Union76.
>
> This is a bit awkward for SQL if the Defendants table and Cases table
> are separate. I, at first, thought about keeping the tables
> separate and doing a series of queries that build upon each othre, but
> this method seems slow performance-wise. Another method I thought of
> is to DENOMARMALIZE the database and simply list all defendants per
> case in a single long text field within [Cases]. Then I can do all the
> "ands" and "ors" in that text field without SQL awkwardness. Performance
> seems fast too.
>
> Do you think this is the best method or is there a better way?
>
> - Steve
Received on Tue Jan 14 2003 - 08:29:04 CST