Relationships between Tables

[Giovanni]

Hi

I have got a problem in establishing the relationship between the tables of
my database. I am creating a database where to put different informations
regarding different agencies in europe and the same agencies in the world.
I have created 7 different tables and each one of them has several field:

-1st- Table (would I choose this one as a mother table?):
"European contact" contain the following field:
ID (I have choosen it as primary key, Is that correct?), Agency name,
Contry, postal address, e-mail, etc...
-2nd- Table (son table):
"Region of operation" contain the following fields:
ID (I have choosen it as primary key, Is that correct?), Region of operation
1(Africa), R. of operation 2(Americas), R. of operation 3(Asia), R. of
operation 4(Europe), R. of operation 5(Asia), etc...
-3thd- Table (son table):
"Country of operation Africa" contain the following field:
ID (I have choosen it as primary key, Is that correct?), Region of operation
1(Africa), form this point a have assigned for each field the name of the
africans countries (I have used as data type yes or not option for those
field, is it the best choice?).
-4,5,6,7th- Tables (sons tables):
I have used the same criteria as the "country of operation in africa" but
referring to the other continents such as America- Asia, Europe, Oceania).

What I might want to know is if I have choose the useful primaries keys.
What kind of relationship should I build in order to be able to perform all
the researches I need in the future between the fied of each table?

I hope I have been clear enough if not I will re-write the question again.

Best regards
Giovanni

 

[John Vinson]

Hi

I have got a problem in establishing the relationship between the tables of
my database. I am creating a database where to put different informations
regarding different agencies in europe and the same agencies in the world.
I have created 7 different tables and each one of them has several field:

-1st- Table (would I choose this one as a mother table?):
"European contact" contain the following field:
ID (I have choosen it as primary key, Is that correct?), Agency name,
Contry, postal address, e-mail, etc...
-2nd- Table (son table):
"Region of operation" contain the following fields:
ID (I have choosen it as primary key, Is that correct?), Region of operation
1(Africa), R. of operation 2(Americas), R. of operation 3(Asia), R. of
operation 4(Europe), R. of operation 5(Asia), etc...
-3thd- Table (son table):
"Country of operation Africa" contain the following field:
ID (I have choosen it as primary key, Is that correct?), Region of operation
1(Africa), form this point a have assigned for each field the name of the
africans countries (I have used as data type yes or not option for those
field, is it the best choice?).
-4,5,6,7th- Tables (sons tables):
I have used the same criteria as the "country of operation in africa" but
referring to the other continents such as America- Asia, Europe, Oceania).

What I might want to know is if I have choose the useful primaries keys.
What kind of relationship should I build in order to be able to perform all
the researches I need in the future between the fied of each table?

Giovanni,

I'm afraid you're on the wrong track.

Each Table should refer to a particular type of "Entity" - real-life
thing, person, or event. Its Primary Key should be a unique identifier
for the entity.

Storing data - such as the country of operation - in a Field Name or
in a Table Name is incorrect design. Data should be stored *in
tables*, not in the *names* of fields or of tables.

I'd suggest starting with four tables:

Regions
Region Text (Primary Key) e.g. "Africa", "Europe", "South America"

Countries
Country Text (Primary Key) e.g. "Senegal"
Region Text <linked to Regions>

Agencies
AgencyID (Primary Key) <autonumber or some unique identifier for the
agency>
AgencyName <this might not be a good primary key since you cannot be
sure that there are not two agencies with the same name>
<other information about the agency, e.g. contact information>

AgencyOperation
AgencyID <link to Agencies>
Country <link to Countries>
<any information about this agency in this country, e.g. address,
contact information, etc.>

For this table you could use a two-field joint Primary Key - select
both AgencyID and Country in table design view and click the Key icon.
This will prevent assigning the same agency to the same country twice.

Some tweaking of this design may be needed but it's hopefully a good
start for you.

John W. Vinson[MVP]
For this table

 

[Giovanni]

Dear Jhon,

Thanks for your prompt answer I have some more question.

When you talk about the table called Regions which should contain:
Region Text (Primary Key) e.g. "Africa", "Europe", "South America"
Do you mean that I can add more than one country a time for each agency or
do you mean that I have to name only one field called "Region Text" and then
put the different countries in a list with lookup wizard? doing this I can
choose only one country a time and that is not what I want because more than
one agency has more than one country of operation. Can you answer me to this
question and then if I will experience other difficuklties I will ask you
more info.
Thank you very much

Giovanni

 

[Giovanni]

Dear all,

I want to ask you help on how to design my database:
I have got the first table (mother) where I have put it all the informations
relating the agencies in europe such as web address, e-@, postal address
etc...

Could you help me to find a way of creating tables in order to store
informations relating the regions (Africa, Asia, America, Oceania, Europe)
where each agency operate (bare in mind that each agency can operate in more
than one country); and another table (one or more?) where to store
informations relating the countries of each region where the agencies
operate(bare in mind that each agencies can operate in more than one country
for each region, and as we told before can operate in different regions at
the same time)?

If you can help me to create this database and also if you can show me which
field is most suitable to assign the primary key and what kind of
relationship between the table I need to create to be able to conduct
researches such as:
e.g. "I need to know for each agencies in wich regions they operate and
whitin each region which countries they operate in."

I hope you can understand the question and that you could give me some
useful information, suggestion and advice.

 

[tina]

Giovanni, you already got the correct table design from John Vinson. you
don't need to enter a record for each *region* an agency operates in, just a
record for each *country* an agency operates in. each country is tied to a
specific region. so if agency A operates in Peru and Sudan, then logically
it operates in South America and Africa, since those are the regions that
those countries are in. using the table structure John gave you, the records
would look like this:

tblRegions
Region
Africa
Europe
South America
(the three rows above are *records in the table*, NOT field names.)

tblCountries
Country Region (foreign key from tblRegions)
Senegal Africa
Sudan Africa
Peru South America
(the three rows above are *records* in the table.)

tblAgencies
AgencyID AgencyName
1 A
2 B
3 C
(the three rows above are *records* in the table.)

tblAgencyOperation
AgencyID Country (foreign key from tblCountries)
1 Peru
2 Senegal
1 Sudan
3 Sudan
(the three rows above are *records* in the table.)

in tblAgencyOperation, you enter one record for each country that each
agency operates in. if one agency operates in 10 different countries, then
you would enter 10 records in the table - one for each agency/country
combination.
because each country is linked to a region (in tblCountries), you can use a
query to show the region(s) that each agency is working in.

hth