Database Design Normalization help

[Billiam]

Thanks for your help so far Duane, I really appreciate it !!!!

It seems that while I may understand that a many to many relationship is
resolved by using an intermediate table or Junction table, with each entity
on the one side of a many relationship to the junction box, my relationship
diagram does not reflect that...which leads me to thinking I am not doing
something correctly.
Can you tell me if this is the correct procedure for the following many to
many realtionship?

tblInstructors
instructorID (PK, autonumber, Long Integer, Yes Index NO Duplicates,ref.
integrity enforced)
Instructor ID# (starts with zeros so a text field)
FirstName
Midname
Lastname

tblAddresses
AddressID (PK, autonumber, Long Integer, Yes Index NO Duplicates,ref.
integrity enforced)
AddressLine1
AddressLine2
AddressLine3
City
ProvinceStateCounty
PostalCode
Country


tblInstructorAddresses
InstructorAddressID (PK, autonumber, Long Integer, Yes Index NO
Duplicates,ref. integrity enforced)
InstructorID (FK, Index Yes No duplicates, JOIN Type 1)
AddressID (FK, Index Yes No Duplicates JOIN Type 1)

As you can see from my relationship report, InstructorID in tblInstructors
is realted one to many with InstructorID in tblInstructorAddresses. It is my
understanding that this is how every many to many realtionship is resolved.
You will note, however, that AddressID from tblAddresses realtes to AddressID
in tblInstructorAddresses as a one to one---not a one to many as I suspect it
should. Why is this happening, Duane? (BTW, I create the realtionship by
taking the primary ID from the table and dropping it on top of the
foreignkeyiD in the junction/intermediate table.)

The really odd thing is that when i click on edit the relationship for
Instructor to instructoraddress it says it is a one to one relationship
between the instructorID's, however, the diagram clearly shows it as a one to
many relationship! Man am I confused!

Billiam

 

[Duane Hookom]

Your design:
InstructorID (FK, Index Yes No duplicates, JOIN Type 1)
AddressID (FK, Index Yes No Duplicates JOIN Type 1)

Your FK should allow duplicates. The fields normally would be Long Integer
depending on the data type in the instructor and address tables. You can
create a unique index on a combination of the InstructorID and AddressID.

 

[Billiam]

Yes, now the relationships are one to many, thanks!

:

You can create a unique index on a combination of the InstructorID and
AddressID.

To clarify,does this mean I do not need the InstructorAddressesID as the
Primary key in tblInstructorAddresses? And if so, does this also mean that
the InstructorID and AddressID foreign keys combo make up the primary key of
tblInstructorAddresses as a composite key?

Billiam

 

[Duane Hookom]

I almost always create a single primary key field in a table which is
generally an autonumber. I do this because I create lots of web pages and I
would like to pass the value of a single field to a web page for editing a
single record.

You could remove the autonumber primary key in a small junction table if you
want and replace it with the combination of the two fields. I wouldn't
because I am lazy (consistently lazy ;-)

 

[Billiam]

Sorry, Duane, I am not sure what you meant by "You can create a unique index
on a combination of the InstructorID and AddressID." as stated a few threads
back...
I am not sure if you were offering another way to index the junction table,
or ? Sorry, I am really new to this, and sometimes all the reading I've done
just makes you more confused!

 

[Duane Hookom]

When you view the indexes in your table design view, you have the opportunity
to select more than one field and provide a single name for the index. You
can set these indexes to not allow duplicates. This is an alternative to the
single field primary key. I usually create a single field primary key and a
unique index on the other foreign key fields.

 

[Billiam]

I am still confused...I tried Access help and found this:
"An index is a Microsoft Access feature that speeds up searching and sorting
in a table." Okay, I get this.

"You can create an index for a field in the Indexes window (Indexes window:
In an Access database, a window in which you can view or edit a table's
indexes or create multiple-field indexes.) Okay, would it have been too much
to show a picture of what the window looks like, or how to get to that
window??? Do they simply mean when you open your table in design view? (On
top you have field name and datatype, on the bottom General and lookup tab?)

of table Design view (Design view: A window that shows the design of these
database objects: tables, queries, forms, reports, macros, and data access
pages. In Design view, you can create new database objects and modify the
design of existing ones.), or by setting the field's Indexed property to Yes."

So do you mean that each field in a table (or query etc) has the possibility
of being indexed? You could use one field and index it. Or you could have
more than one field, and therefore indexes.

If so, here is where I am confused:

When you view the indexes in your table design view (OK at this point) you
have the opportunity to select more than one field (okay to this point) and
provide a single name for the index. What do you mean by provide a single
name for the index---the Table name?

Sorry Duane, I hope you are not pulling your hair out, but I really need to
undestand this basic stuff correctly, as I am mostly following a recipe right
now---I know I need to make a cake, that it has ingredients, they have to get
mixed in certain relationships, etc etc...but I still do not have the reasons
for baking etc down pat!

BTW, if you answer, "Did someone say cake?" I would not blame you ! ;-]

Billiam