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
|
|||
|
|||
Design Help
I've been reading about tables, primary keys, and data normalization (i'm
even enrolled in a course)...unfortunately I need a database on the job front quicker than I can learn in my course. So if anyone would be so kind to help. I think I'm confused with the tables and how the user will enter things via the form but I'm getting ahead. I have regions, countries in those regions, and level of support. I'm having trouble connecting these. I uploaded all the countries into one table, Country I uploaded the regions into another table, Regions I have another table outlining levels of support (5 levels), Support. I want to have this information populated and then have users maintain it. So I'm wondering how do I show that Austria in EMEA receives Level 4 Support? Or do I have a table that combines all these tables? I'm guessing primary key (PK) and foreign keys (FK) but again I'm confused. I have it set as autonumber for primary keys but do I have to look up the primary key in one table and go plug it into the foreign key field of another? Could take forever Tables: Region RegionID (PK), Region Country CountryID (PK), Country, RegionID (FK) Support Support Level not sure, guessing I include RegionID and CountryID as FK's? Once I get the design straight guess i'll need to figure out the form interface for the user. Sorry for the elementary questions, trying to get out of excel mode. Thanks for the help! |
#2
|
|||
|
|||
Design Help
jenniferspnc wrote:
I've been reading about tables, primary keys, and data normalization (i'm even enrolled in a course)...unfortunately I need a database on the job front quicker than I can learn in my course. So if anyone would be so kind to help. I think I'm confused with the tables and how the user will enter things via the form but I'm getting ahead. I have regions, countries in those regions, and level of support. I'm having trouble connecting these. I uploaded all the countries into one table, Country I uploaded the regions into another table, Regions I have another table outlining levels of support (5 levels), Support. I want to have this information populated and then have users maintain it. So I'm wondering how do I show that Austria in EMEA receives Level 4 Support? Or do I have a table that combines all these tables? I'm guessing primary key (PK) and foreign keys (FK) but again I'm confused. I have it set as autonumber for primary keys but do I have to look up the primary key in one table and go plug it into the foreign key field of another? Could take forever Tables: Region RegionID (PK), Region Country CountryID (PK), Country, RegionID (FK) Support Support Level not sure, guessing I include RegionID and CountryID as FK's? Once I get the design straight guess i'll need to figure out the form interface for the user. Sorry for the elementary questions, trying to get out of excel mode. Thanks for the help! I'm novice myself so I can relate. I have a few questions going here too. I think you're on the right track. Only suggestion I would make is the Support Levels should be in its own table (a listing defining all the Support Levels that are possible). Then create another table to describe the combination of Country(FK) and Support Level(FK) for that country. That new table is where you will do all the work - this can be the basis of your forms, queries, etc. Once you have your tables set up relationships to connect the PK and FK between tables to enforce ref integrity. You have already defined the relation between Country and Region so you don't need to refer to Region in your new table. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/200803/1 |
#3
|
|||
|
|||
Design Help
"jenniferspnc" wrote in message ... I've been reading about tables, primary keys, and data normalization (i'm even enrolled in a course)...unfortunately I need a database on the job front quicker than I can learn in my course. So if anyone would be so kind to help. I think I'm confused with the tables and how the user will enter things via the form but I'm getting ahead. I have regions, countries in those regions, and level of support. I'm having trouble connecting these. I uploaded all the countries into one table, Country I uploaded the regions into another table, Regions I have another table outlining levels of support (5 levels), Support. I want to have this information populated and then have users maintain it. So I'm wondering how do I show that Austria in EMEA receives Level 4 Support? Or do I have a table that combines all these tables? I'm guessing primary key (PK) and foreign keys (FK) but again I'm confused. I have it set as autonumber for primary keys but do I have to look up the primary key in one table and go plug it into the foreign key field of another? Could take forever Tables: Region RegionID (PK), Region Country CountryID (PK), Country, RegionID (FK) Support Support Level not sure, guessing I include RegionID and CountryID as FK's? Once I get the design straight guess i'll need to figure out the form interface for the user. Sorry for the elementary questions, trying to get out of excel mode. Thanks for the help! Do all countries in a region have the same support level? If yes, put the support level at the region level. If each country within a region can have different support levels, then put the support level at the country level. Good Luck, Evan |
#4
|
|||
|
|||
Design Help
So do I have to enter the autonumber PK from one table into the FK column of
the related tables? Hoping there was an easier way but perhaps not but I need to show the relationship so to build queries later. And support is at the country level, so I'm assuming that I don't need a separate table for Support? Just add a column in the Country table? Thanks again for the help. "Evan Keel" wrote: "jenniferspnc" wrote in message ... I've been reading about tables, primary keys, and data normalization (i'm even enrolled in a course)...unfortunately I need a database on the job front quicker than I can learn in my course. So if anyone would be so kind to help. I think I'm confused with the tables and how the user will enter things via the form but I'm getting ahead. I have regions, countries in those regions, and level of support. I'm having trouble connecting these. I uploaded all the countries into one table, Country I uploaded the regions into another table, Regions I have another table outlining levels of support (5 levels), Support. I want to have this information populated and then have users maintain it. So I'm wondering how do I show that Austria in EMEA receives Level 4 Support? Or do I have a table that combines all these tables? I'm guessing primary key (PK) and foreign keys (FK) but again I'm confused. I have it set as autonumber for primary keys but do I have to look up the primary key in one table and go plug it into the foreign key field of another? Could take forever Tables: Region RegionID (PK), Region Country CountryID (PK), Country, RegionID (FK) Support Support Level not sure, guessing I include RegionID and CountryID as FK's? Once I get the design straight guess i'll need to figure out the form interface for the user. Sorry for the elementary questions, trying to get out of excel mode. Thanks for the help! Do all countries in a region have the same support level? If yes, put the support level at the region level. If each country within a region can have different support levels, then put the support level at the country level. Good Luck, Evan |
#5
|
|||
|
|||
Design Help
----- Original Message -----
From: "jenniferspnc" Newsgroups: microsoft.public.access.tablesdbdesign Sent: Wednesday, March 26, 2008 8:29 AM Subject: Design Help So do I have to enter the autonumber PK from one table into the FK column of the related tables? Hoping there was an easier way but perhaps not but I need to show the relationship so to build queries later. Yes. And support is at the country level, so I'm assuming that I don't need a separate table for Support? Just add a column in the Country table? Thanks again for the help. Creating a separate table for Support would make it easier if you ever had to change the string the describes the Support Level. For example, let's say you wanted to change Support from "24/7" to "Premium" you would only have to change it in the Support lookup table. "Evan Keel" wrote: "jenniferspnc" wrote in message ... I've been reading about tables, primary keys, and data normalization (i'm even enrolled in a course)...unfortunately I need a database on the job front quicker than I can learn in my course. So if anyone would be so kind to help. I think I'm confused with the tables and how the user will enter things via the form but I'm getting ahead. I have regions, countries in those regions, and level of support. I'm having trouble connecting these. I uploaded all the countries into one table, Country I uploaded the regions into another table, Regions I have another table outlining levels of support (5 levels), Support. I want to have this information populated and then have users maintain it. So I'm wondering how do I show that Austria in EMEA receives Level 4 Support? Or do I have a table that combines all these tables? I'm guessing primary key (PK) and foreign keys (FK) but again I'm confused. I have it set as autonumber for primary keys but do I have to look up the primary key in one table and go plug it into the foreign key field of another? Could take forever Tables: Region RegionID (PK), Region Country CountryID (PK), Country, RegionID (FK) Support Support Level not sure, guessing I include RegionID and CountryID as FK's? Once I get the design straight guess i'll need to figure out the form interface for the user. Sorry for the elementary questions, trying to get out of excel mode. Thanks for the help! Do all countries in a region have the same support level? If yes, put the support level at the region level. If each country within a region can have different support levels, then put the support level at the country level. Good Luck, Evan |
#6
|
|||
|
|||
Design Help
Do you need to record when a country is given a different level of support
(perhaps with a date when the support level changes)? If yes, then you need a seperate CountrySupport table so you can record this change. It will contain its own primary key, and the FK fields CountryID and SupportID because the Country will have to be added more than once. If Not, I still recommend having a seperate Support Table. It does mean creating an extra combo box in your form but it can be useful in limiting the choices that a user can make when inputting the data. You could also include in the support table, fields which, for instance, define the criteria for a specific level of support. If you find at a later stage, this is superfluous, you can always replace the ID number with a Support Level number using an Update query. If a country only ever has one level of support at a time and you don't need to record when that support is changed (so that Country only ever has to be added once to the Country table) then SupportID can be a ForiegnKey field in the Country table. Evi "jenniferspnc" wrote in message ... So do I have to enter the autonumber PK from one table into the FK column of the related tables? Hoping there was an easier way but perhaps not but I need to show the relationship so to build queries later. And support is at the country level, so I'm assuming that I don't need a separate table for Support? Just add a column in the Country table? Thanks again for the help. "Evan Keel" wrote: "jenniferspnc" wrote in message ... I've been reading about tables, primary keys, and data normalization (i'm even enrolled in a course)...unfortunately I need a database on the job front quicker than I can learn in my course. So if anyone would be so kind to help. I think I'm confused with the tables and how the user will enter things via the form but I'm getting ahead. I have regions, countries in those regions, and level of support. I'm having trouble connecting these. I uploaded all the countries into one table, Country I uploaded the regions into another table, Regions I have another table outlining levels of support (5 levels), Support. I want to have this information populated and then have users maintain it. So I'm wondering how do I show that Austria in EMEA receives Level 4 Support? Or do I have a table that combines all these tables? I'm guessing primary key (PK) and foreign keys (FK) but again I'm confused. I have it set as autonumber for primary keys but do I have to look up the primary key in one table and go plug it into the foreign key field of another? Could take forever Tables: Region RegionID (PK), Region Country CountryID (PK), Country, RegionID (FK) Support Support Level not sure, guessing I include RegionID and CountryID as FK's? Once I get the design straight guess i'll need to figure out the form interface for the user. Sorry for the elementary questions, trying to get out of excel mode. Thanks for the help! Do all countries in a region have the same support level? If yes, put the support level at the region level. If each country within a region can have different support levels, then put the support level at the country level. Good Luck, Evan |
Thread Tools | |
Display Modes | |
|
|