Design Help

[jenniferspnc]

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!

 

[DougW via AccessMonster.com]

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.

 

[Evan Keel]

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

 

[jenniferspnc]

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]

----- Original Message -----
From: "jenniferspnc" <[email protected]>
Newsgroups: microsoft.public.access.tablesdbdesign
Sent: Wednesday, March 26, 2008 8:29 AM
Subject: Re: 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.

 

[Evi]

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