Help with normalization

[Irshad Jamal]

Dear Forum,

I am designing a projects database and that requires the following information to be collected. I would appreciate any feedback on this.

1) Project_Sector (Agriculture, Water Supply, Energy)
2) Sub_Sector(Livestock or Crop, Drinking or Irrigation, Alternative or Mini Hydro)
3) Sub_Project# (Every project has a unique number like KHJ-02-JAP-EDU-042)
4) Sub_Project_Title
5)Name of Donor
6)Province Name where project was implemented
7)District name where project was implemented
8)Sub_District where project was implemented
9) Village name where project was implemented
10)Number of beneficiaries
11)Number of women beneficiaries
11)Start_Date of project
12)End date of project
13)Budget_USD
14)Brief description of the project


I normalized the database my breaking it into 3 tables;

Project Table:

Project_Sector_ID (PK)
Project_Sector

Sub-ProjectTable:

Sub_Sector_ID + Project_Sector_ID (Composite primary key)
Sub_Project_Num
Sub_Project_Title
Brief_Project_Desc
Beneficiaries
Women_Beneficiaries
Start_Date
End_Date
Budget_USD

Sub-Project-Location Table:

Sub_Sector_ID + Project_Sector_ID (Composite primary key)
Province_ID
District_ID
Sub_District_ID
Village_ID

In addition to the above tables, I have the following look-up tables:

1)Project_Sector_List:

Project_Sector_ID: 1
Project_Sector_Name: Water Supply


2)Sub_Sector_List:

Sub_Sector_ID: 1
Sub_Sector_Name: Drinking Water


3)Donor_List:

Donor_ID: 1
Donor_Name: European Union

4)Province_List:

Province_ID: 1
Province_Name: Sughd

5)Distrct_List:

District_ID: 1
District_Name: Ayni

6)Sub_District_List:

Sub_District_ID: 1
Sub_District_Name: Baljuvon

7)Village_List:

Village_ID: 1
Village_Name: Shaartuz


The relationships are as follows:

One project has many sub projects (1-m)

One subproject can be implemented in many locations (1-m)


Thanks in advance.

Irshad