Relationship with many levels.. Help Appreciated

[Guest]

I have a database and cannot set up tables and relationships to make it work.

Table INFO:
First Table Includes: Region ID#(this is the primary key) and 3 other fields
Second Table Includes: Auto ID(autonumber and primary key) District ID# and
Region ID#
Third Table: Patrol ID# District ID# and 5 more fields with info on patrolling
Fourth Table: Contractor ID# District ID# and 5 more fields with info on
contracting

Here is the problem:
I need a 3 layer database:
There are 30 regions which have 125 districts each. Each district may need
more than one entry. Then for all of these districts is information from 2
other tables, the patrol table and the contractor table.
"I see it like a large expanding cone, where it begins with region and
expands out to contractor info and patroller info"

I want to be able to change regions and have the districts be separate for
each region. I also want to be able to change the contractor and patrol
information and have it seperate for each district. yet it still must relate
back to regions.

I have tried many different setups, but the problem seems to be getting
around the primary key and allowing duplicate values for the 2
tables(contractor and patroller) when linking them to the districts, and
still having the districts associated with the regions.

I work for a large company and have been put in charge of date integration,
but this problem is setting me back. I would would greatly accept any
helpful advice.
Please e-mail me with information at (e-mail address removed).

 

[George Nicholson]

I hope I understood you correctly. A little guesswork is involved in the
following:

Each district may need more than one entry.

You mean existing DistrictIDs aren't unique and might occur in more than one
region? In that case you should establish a unique index on the
*combination* of RegionID and DistrictID to ensure that any given DistrictID
remains unique within a Region. I would call my autonumber field something
like RegDistID and make sure you use that for relationships with your
"downstream" data.

the problem seems to be ... allowing duplicate values for the 2
tables(contractor and patroller)

Well, no need for those tables to have duplicate values. One record in
Contractors for each Contractor. One record in Patroller for each Patroller
(Patrol?). NO District or Region info in these tables.

2 additional tables, to connect Districts with Contractors & Patrollers:
DistrictContractors: Unique index on combination of RegDistID and
ContractorID
DistrictPatrollers: Unique index on combination of RegDistID and
PatrolID (PatrollerID?)

This way a single Contractor or Patroller can be "assigned" to as many
RegDistIDs as is appropriate, but can't be assigned to any specific
RegDistID more than once.

HTH,

 

[Guest]

Thank you George,

I think I understand what I need to do. What I was trying to do and I think
you understood, is to have a 3 level relationship:

Contractor
Patroller
District Contractor
Region District Patroller
District Contractor
Patroller

Where it is broken down into Region, which has many districts, and each
district has both contractor and patroller information linked to them, which
in turn must also only be for the region selected.
"so each region has many districts and
distinct informaion on patroller and contractor"

I found the problem to be that I could not have primary keys for each table
because they do not allow duplicate values...

I know that this is a simple problem to fix, but I do not know if I will be
able to figure it out through text reading. I would be in debt to you
greatly if you could create a simple database setup based on what I have
explained and e-mail it to me.
I can add all the other non important fields after. It only takes me a
couple of minutes to create the setup myself, but cannot get past this
problem. My e-mail is (e-mail address removed), or
(e-mail address removed). I will attempt to correct this problem on
my own, but fear I will have issues.

My thanks.