I am designing a projects database 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;
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
With this construct
1,2,& 3 describe a unique object. 4 also describes the object.
There is some thought that says these items should not be a unique key. If you erroneously use one and need to change it it requires everywhere that the key is present to be changed to correct it. In many instances a unique system created key is better. In this case the key would be very long perhaps and it a created key might work better.
Why is the sub-project key so complicated and long? does it have the Project sector and Sub_sector in it? It it does it may be the primary key??
Also projects like Agriculture and sectors could be a code also along need to be a code with a code lookup table. If you spell it out then it is not a valid key because it can be mispelled.
Previous Topic
