Re: Deadly sins againts database performance/scalability

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Thu, 27 Nov 2003 16:14:09 -0800
Message-ID: <1069978478.27427@yasure>

ctcgag_at_hotmail.com wrote:

> Daniel Morgan <damorgan_at_x.washington.edu> wrote:
>

>>Again, today, I've been called into look at a database in which it is
>>impossible to connect related events.
>>
>>Example:
>>1. Cases have a case number
>>2. Citations have a case number and a citation number
>>3. Fees paid for citations have a case number and a payment number
>>
>>If you can't see what's wrong ... you're part of the problem.   ;-)

>
>
> I'm not sure if I'm part of the problem or not. If the case-citation
> relationship is one-to-one but optional, I don't see the problem. If it's
> one to many, I do (unless you say that money is fungible and it doesn't
> matter what the payments are for as long as it adds up to the correct
> amount)
>
> Xho
>

You may not be part of the problem but you are a strong candidate. ;-)

With the above you can link citations to cases. And you can link fees to cases.
But you can not tell which fee goes with which citation.

The following simple change would provide referential integrity.

1. Cases have a case number
2. Citations have a case number and a citation number
3. Fees paid for citations have a citation number and a payment number.

The fees are linked to the case by means of the citation number providing a complete referential join.

This is a critical skill. And one I see all too rarely.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Thu Nov 27 2003 - 18:14:09 CST