Help with table structure

[SF]

I have 5 tables as below:

tblProvinces
ProvinceID (PK)
ProvinceName
...
tblDistricts
DistrictID
ProvinceID (FK to tblProvince)
DistrictName
tblCommunes
CommuneID
CommuneName
DistrictID (FK to tblDistrict)

tblVillages
VillageID
Villagename
CommuneID (FK to tblCommune)

tblProject
ProjectID
ProjectName
ProvinceID
DistrictID
CommuneID
VillageID
ProjectCost
....

This work OK for a project for one single location (one province, district,
commune and village). Now my supervisor want to change the structure. He
wants a project would cover to one province, many districts and may or may
not have any communes information. I come up by adding another table as
below

tblProjectLocations
ProjectID
ProvinceID
DistrictID
CommuneID

I would appreciate advide from this group for the correct table structure.
Also with this tblProjectLocations table, I cannot enforced the relationship
to CommuneID.

SF

 

[Allen Browne]

SF, one option might be to create a generic Locations table, and put all the
locations in there. Then join the table to itself, so that one location can
be contained within another location. Ultimately, this allows you to use any
level of location as a foreign key, as they are all in the one table.

tblLocationType
LocationTypeID Text primary key
This table contains records such as "Province", "District", ...

tblLocation
LocationID p.k.
LocationName Text
LocationTypeID Text Foreign key to
tblLocationType.LocationTypeID
ParentLocationID Number Foreign key to tblLocation.LocationID

To create the relationship, drag a second copy of tblLocation into the
Relationships window (Tools menu.) Access will alias it as tblLocation_1.
You can now create the relation (self-join) with referential integrity.

Now you can create the relation between tblLocation and tblProjectLocations,
with RI.