Help with normalizing database

[SF]

Hi,

I have 5 tables, 4 of which use for lookup. Those 4 table are tblProvinces,
tblDistricts, tblCommunes, tblVillages.

tblProvinces
Pv_ProvinceID PK
Pv_Name_e (Name in English)
Pv_Name_k (Name in Khmer)
...

tblDistricts
DistrictID PK
ProvinceID FK from tblProvinces
DistrictName_e
....

tblCommunes
CommuneID PK
DistrictID FK from tblDistrict
CommuneName_e
....
tblVillages
VillageID PK
CommuneID FK from tblCommunes
VillageName_e

All the above 4 tables use for information lookup. There is a project table
as below:

tblProjects
ProjectID
ProjectName
ProvinceID FK from tblProvinces
DistrictID FK from tblDistricts
CommuneID FK
VillageID FK
....

My question is whether to retain only VillageID in the tblProjects and
discard the other 3 fields (ProvinceID, DistrictID, and CommuneID) or retain
all 4 fields?

SF

 

[Jeff Boyce]

One way of looking at it is that the Province, District and Commune (FK)
fields in your Project table are redundant, since you can use a query to
derive all those from Village.

If performance suffers, but improves by including those fields, your design
is "less-than-fully-normalized", but necessary.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/