[prog] normalization and databases
jarich at perltraining.com.au
Mon Oct 18 12:00:12 EST 2004
I don't remember my normal forms, but the principals I use are all based
on the same aim - to avoid redundancy. So I'll offer a solution with
this in mind. Do note that this might ruin the execise for you as I do
offer a full solution. :)
> Donations(Donor_Number, Donor_Name,Donor_Phone, Classification_Code,
> Classification_Name, Exempt_Status,(Check_Number, Date_Received,
> Amount, Fund_Number, Fund_Name, Fund_Directory)
> This is a table concerning information about donors and their
> contributions to a non-profit
> organization. Each donation is made to a particular fund (Food Bank,
> Medical Care, etc_).
> In addition, each donor is assigned to a particular class of donors (
> personal, corporate, etc).
> All donations are made by check. The following dependencies exist in Donations..
> Donor_Number -> Donor_Name, Donor_Phone, CLassification_Code,
> Classification_Name, Exempt_Statis
> Classification_Code -> Classification_Name
> Donor_Number, Check_Number -> Date_Received, Amount, Fund_Number,
> Fund_Name, Fund_Director
> Fund_Number -> Fund_Name, Fund_Director
First of all. It seems logical that a donor could make more than one
donation in the course of their lives. Thus, Donor information should
be in a table of its own:
Donors ( Donor_Number, Donor_Name, Donor_Phone ... )
Classification seems to be tied in with donors and probably exempt
status too, giving us:
* Donors ( Donor_Number, Donor_Name, Donor_Phone,
* Classification_Code, Exempt_Status )
* Primary key: Donor_Number
* Foreign key: Classification_Code references Classifications
The primary key uniquely identifies the donor entry. This means that
there could be two Joan Smiths, but we'd know they were different
because of the Donor_Number. If we want to allow donors to have
multiple phone numbers at some point, then we'd need to pull the
Donor_Phone bit out and have a table which associated the phone number
with the Donor_Number.
I've left off including Classification_Name in the table, because we're
now going to create a table to map Classification names and numbers
* Classifications( Classificiation_Code, Classification_Name )
* Primary Key: Classification_Code.
If we were to include both classification code and classification name
in the donor entry then we run the risk that one donor entry could have
classification code 8 and classification name "personal" whereas a
different entry could have classification code 8 and classification name
"corporate". Ideally the code should uniquely identify the name.
To look at the actual donations we need a donations table which has
information about the cheques in it.
* Donations( Donor_Number, # who the donation is from
* Cheque_Number, # which donation from this donor
* Date_Received, # multiple donations in one day are
* # allowed
* Primary Key: Donor_Number, Cheque_Number
* Foreign Key: Donor_Number references Donors
* Foreign Key: Fund_Number references Funds
Once again, since the Fund_Number should be able to tell us information
about the Fund_Name and Director, we can leave that out of this table.
I've added some comments in so as to explain the primary key. Assuming
people stay with one bank, then their cheque numbers are always unique
(and incrementing). Thus if I give you cheque number 7 then I'll not be
giving either you or someone else cheque number 7 in the future.
However, you may have a cheque number 7 all of your very own. So in
order to distinguish my cheque 7 from your cheque 7, and my cheque 7
from my cheque 8, we need a dual primary key. My Donor_Number AND my
Cheque_Number uniquely distinguish this record.
We omit the Date_Received from the primary key, as the cheque_number and
donor_number are sufficient.
* Funds ( Fund_Number,
* Fund_Director )
* Primary Key: Fund_Number
This should be pretty straight forward. If the director ever changes
then it only gets updated in this table. Easy stuff. Note however that
we're not allowing funds to have multiple directors. IF that is
required then we'll need to take the director out of this table and
create a table that links fund directors and funds.
You'll notice that most of my tables specified above are based almost
wholly on your restrictions. This is normal. I've also mentioned some
points where there might in the future be further restrictions, such as
multiple phone numbers for donors, or multiple directors for funds.
I've found a great discussion on normal forms over here:
And it would appear that I've sorted these into 3rd Normal form
(hooray!). 3rd Normal form is what most people use, so its pretty easy
to get used to.
If you have any further questions please ask.
All the best,
("`-''-/").___..--''"`-._ | Jacinta Richardson |
`6_ 6 ) `-. ( ).`-.__.`) | Perl Training Australia |
(_Y_.)' ._ ) `._ `. ``-..-' | +61 3 9354 6001 |
_..`--'_..-_/ /--'_.' ,' | contact at perltraining.com.au |
(il),-'' (li),' ((!.-' | www.perltraining.com.au |
More information about the Programming