Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Normalizing vs de-normalizing

Re: Normalizing vs de-normalizing

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Thu, 16 Jan 2003 09:59:58 -0800
Message-ID: <3E26F31E.F2D2B70C@exesolutions.com>


Steve Quezadas wrote:

> I have a legal database with a [Cases] table and a [Defendants] table and
> they are both linked together with a [CaseNumber] field.There are usually
> about 40 defendants or so for every case, and it's a "one to many
> relationship". 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 MUST 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 tables that list case numbers that build/exclude upon
> each other for ecah criteria, 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 the [Cases]
> table with each name separated by a token (in this case "~"). Then I can
> do all the "ands" and "ors" in that single text field without SQL awkwardness.
> Performance seems fast too.
>
> Do you think this is the best method or is there a better way?
>
> - Steve

What links the two tables? Just inventing an aribtrary field for demo purposes:

SELECT c.case_number
FROM cases c, defendants d
WHERE c.id = d.id
AND d.name = 'UNION76'

will give you all case numbers where UNION76 is definitely the defendant.

Then put this into another query as part of the WHERE clause:

SELECT *
FROM cases c, defendants d
WHERE c.id = d.id
AND c.id IN (

   SELECT c.case_number
   FROM cases c, defendants d
   WHERE c.id = d.id
   AND d.name = 'UNION76');

If this doesn't do it please provide information on the foreign key between the two tables and the correct column names.

Daniel Morgan Received on Thu Jan 16 2003 - 11:59:58 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US