db setup help - am i on the right track?

[nd]

**** Post for FREE via your newsreader at post.usenet.com ****



i am trying to create a db for teachers. Each teacher has three main
catagories: location, age, cert. Each teacher can work in multiple
location. Each teacher can have multiple certs. Each teacher works
with multiple age groups. I need to beable to enter teachers info into
the db, and run form-queries (queries based on forms) with the criteria
for each category selected by the user.

need a little help on setting things up to start with. I have an outline
(or scratch db called normalized) here: http://www.pfll.org/dbtesting/

thanx


-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
*** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
http://www.usenet.com
Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

 

[TC]

nd, I answer a fair few questions in the Access newsgroups, but I will not
go outside the newsgroup to get information that the poster could have
provided in his post, to begin with. Others may differ, but I suggest you
re-post with some details of your database structure. Use the example format
shown below. That effort is your contribution towards getting your questions
answered. Otherwise, it is like you are saying, "Hey, I can't be bothered to
tell you about my database - >you< make the effort to download it [perhaps
over a slow modem link], open it [running the risk that it contains a macro
virus], then wade through it [looking for information that could just as
well have been provided in the initial post], for me!"

Example format:

tblPerson - one record for each person known to the system
PersonID (autonumber) (primary key)
fornmame (text)
surname (text)
dateofbirth (date)
etc.

HTH,
TC
(off for 2 days)

 

[nd]

**** Post for FREE via your newsreader at post.usenet.com ****


Tbl-instrct - one record for each

instructor
Name (Text)(primary key)
Phone (number)

Tblregion - one record for each

region an instructor might work
Regionid (text)(primary key)
Regionname (text)(unique

enough to be a primary key)

Tblcert - one record for each

possible cert. an instructor may

have
Certid (text)(primary key)
Certname (text)

Tblage - one record for each static

age range an instructor may work

with
Ageid (number)(primary key)
Agerange (text)



I only have four normalized tables,

i believe. Although the instructor

table may contain a little more info

(i.e. soc.s.#, address, ect) i dont

believe any of the additional data

will effect the db's functionality.

I have started creating some join

tables and see the need for more.

Basically any instructor can have

multiple certs., multiple regions

the work in, and multiple age groups

they work with. I will eventually

need to beable to create a form that

will allow a user to select which

criteria a teacher must meet

(region, agegroup, and upto three or

four specific certs.). Then have

only those instructors who meet the

criteria will be listed (via a query

or report) .

The join tbls i have so far are:

Tbl-instregion - each record has

multiple regions, each allowing

dropdown selection from the list of

regions in tblregion. Each

instructor can work in multiple

regions
Name (text)(primary key)
region1 (text)
region2 (text)
region3 (text)

Tbl-instcert - each record has each

possible cert listed across the top

w/an associated y/n box
Name (text)(primary key)
Readingcert (y/n)
Elementarycert (y/n)
Mathcert (y/n)
??I am not sure if i am setting the

tbl-instcert up correctly, not sure

how to associate the y/n boxes w/the

tblcert except maybe by query ?

my broader question is Each teacher

has three main catagories: location,

age, cert. Each teacher can work in

multiple locations. Each teacher

can have multiple certs. Each

teacher works with multiple age

groups. I need to beable to enter

teachers info into the db, and run

form-queries (queries based on

forms) with the criteria for each

category selected by the user.

need a little help on setting things

up to start with. I have an outline
(or scratch db called normalized)

here: http://www.pfll.org/dbtesting/
or please use the descriptions i

have provided above (i hope i have

explained somewhat clearly)

thnx



-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
*** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
http://www.usenet.com
Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

 

[nd]

**** Post for FREE via your newsreader at post.usenet.com ****


Tbl-instrct - one record for each

instructor
Name (Text)(primary key)
Phone (number)

Tblregion - one record for each

region an instructor might work
Regionid (text)(primary key)
Regionname (text)(unique

enough to be a primary key)

Tblcert - one record for each

possible cert. an instructor may

have
Certid (text)(primary key)
Certname (text)

Tblage - one record for each static

age range an instructor may work

with
Ageid (number)(primary key)
Agerange (text)



I only have four normalized tables,

i believe. Although the instructor

table may contain a little more info

(i.e. soc.s.#, address, ect) i dont

believe any of the additional data

will effect the db's functionality.

I have started creating some join

tables and see the need for more.

Basically any instructor can have

multiple certs., multiple regions

the work in, and multiple age groups

they work with. I will eventually

need to beable to create a form that

will allow a user to select which

criteria a teacher must meet

(region, agegroup, and upto three or

four specific certs.). Then have

only those instructors who meet the

criteria will be listed (via a query

or report) .

The join tbls i have so far are:

Tbl-instregion - each record has

multiple regions, each allowing

dropdown selection from the list of

regions in tblregion. Each

instructor can work in multiple

regions
Name (text)(primary key)
region1 (text)
region2 (text)
region3 (text)

Tbl-instcert - each record has each

possible cert listed across the top

w/an associated y/n box
Name (text)(primary key)
Readingcert (y/n)
Elementarycert (y/n)
Mathcert (y/n)
??I am not sure if i am setting the

tbl-instcert up correctly, not sure

how to associate the y/n boxes w/the

tblcert except maybe by query ?

my broader question is Each teacher

has three main catagories: location,

age, cert. Each teacher can work in

multiple locations. Each teacher

can have multiple certs. Each

teacher works with multiple age

groups. I need to beable to enter

teachers info into the db, and run

form-queries (queries based on

forms) with the criteria for each

category selected by the user.

need a little help on setting things

up to start with. I have an outline
(or scratch db called normalized)

here: http://www.pfll.org/dbtesting/
or please use the descriptions i

have provided above (i hope i have

explained somewhat clearly)

thnx


-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
*** Usenet.com - The #1 Usenet Newsgroup Service on The Planet! ***
http://www.usenet.com
Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

 

[TC]

nd, I've given some comments throughout. Hope they help. TC.

**** Post for FREE via your newsreader at post.usenet.com ****

Tbl-instrct - one record for each instructor
Name (Text)(primary key)
Phone (number)

A person's name is seldom if ever acceptable as a primary key. What if you
get two instructors with the same name? You need a primary key that is
guaranteed to have a different value for every instructor in all cases. An
autonumber field is a common choice here. Remove the PK selection from Name.
Add InstructID (autonumber) as the PK. Also, not that you would normally
want seperate fields for the forname & surname. Otherwise, it is difficult
to sort by surname,forname etc.

Tblregion - one record for each region an instructor might work
Regionid (text)(primary key)
Regionname (text)(unique
enough to be a primary key)

If regionid is a unique value for reach region, it is suitable as the
primary key. In that case, you do not need regioname as a second primary
key. Each table has just one primary key. A primary key can have more than
one field - a so-called "composite" primary key - but it is still
notionally, a single primary key. You only need a single-field
(non-composite) primary key here.

You will also need a table to relate instructors to regions. The structure
of that table is:

tbl(whatever) - one record for each instructor/region combination
InstructorID ( composite )
RegionID ( primary key )

Tblcert - one record for each possible cert. an instructor may have
Certid (text)(primary key)
Certname (text)

Seems ok! You'll also need a table to relate Instructors to certs. The
structure of that table is:

tbl(whatever) - one record for each instructor/cert combination
InstructorID ( composite )
CertID ( primary key )

Tblage - one record for each static age range an instructor may work with
Ageid (number)(primary key)
Agerange (text)

Seems fine. You'll need a linking table as above.

I only have four normalized tables,
i believe. Although the instructor
table may contain a little more info
(i.e. soc.s.#, address, ect) i dont
believe any of the additional data
will effect the db's functionality.

Sounds ok - with the exception of your need to add a proper primary key
field (eg. InstructoID).

I have started creating some join
tables and see the need for more.
Basically any instructor can have
multiple certs., multiple regions
the work in, and multiple age groups
they work with.

I've suggested those above.

I will eventually
need to beable to create a form that
will allow a user to select which
criteria a teacher must meet
(region, agegroup, and upto three or
four specific certs.). Then have
only those instructors who meet the
criteria will be listed (via a query
or report) .

The join tbls i have so far are:

Tbl-instregion - each record has
multiple regions, each allowing
dropdown selection from the list of
regions in tblregion. Each
instructor can work in multiple

regions
Name (text)(primary key)
region1 (text)
region2 (text)
region3 (text)

Nooooo! The presence of a repeating field (region 1-4) is a clear
indication of a denormalized table. See the proper table structure suggested
above.

nd, I hope this gives you some ideas to ponder! You are >close< to being on
the right track - but not there yet. You need to do more reading on the
topic of database normalization. Start here for a dry, but instructive,
read:

http://support.microsoft.com/support/kb/articles/Q100139.ASP

HTH,
TC