Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Normalizing vs de-normalizing
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