If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Relationships between Tables
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 |
#2
|
|||
|
|||
On Tue, 1 Mar 2005 08:19:06 -0800, "Giovanni"
wrote: 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 |
#3
|
|||
|
|||
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 "John Vinson" wrote: On Tue, 1 Mar 2005 08:19:06 -0800, "Giovanni" wrote: 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 |
#4
|
|||
|
|||
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. "Giovanni" wrote: 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 "John Vinson" wrote: On Tue, 1 Mar 2005 08:19:06 -0800, "Giovanni" wrote: 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 |
#5
|
|||
|
|||
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 "Giovanni" wrote in message ... 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. "Giovanni" wrote: 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 "John Vinson" wrote: On Tue, 1 Mar 2005 08:19:06 -0800, "Giovanni" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Can't drag tables in relationships view | WC | Running & Setting Up Queries | 3 | January 27th, 2005 04:15 AM |
double tables in relationships | Joel | General Discussion | 2 | October 15th, 2004 07:28 AM |
More assistance with Many to Many Relationships | Lynn | Database Design | 3 | July 9th, 2004 04:22 PM |
Mutliple Tables lookup? | Westley | Database Design | 4 | June 15th, 2004 01:07 AM |