Re: Normalizing vs de-normalizing

Steve Quezadas <steveeq2_at_tripperjones.com> wrote:
> 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.

If you just want the casenumber (which I assume is the primary key of case and a relational key in defendant.), you can do this:

select a.casenumber from defendants a, defendants b where

  a.casenumber=b.casenumber and
  a.name='Union76' and
  b.name in ('Arco', 'Shell', 'Exxon');

> 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.

Should pretty quick if the right index are built.

> 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.

Huh? That seems pretty awkward to me. How do you do that? Something like:

select casenumber from Case where Defends like '%,Union76,%' and   (Defends like '%,Arco,%' or Defends like '%,Shell,%' or    Defends like '%,Exxon,%');

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB