Address Design Structure

[Billiam]

With the help of this forum, I am trying to correctly design my tables to be
normalized. I would like to break my design down into sections to work on
just for ease of discussion and for the rest of people trying to figure this
all out it may make it clearer to them also. I am a beginner in Access and
TRYING HARD to figure it out so that I can properly design a database instead
of the 55 field non-normlized one I currently use-0

Our non-profit organization has instructors. They may have many addresses
and therefore address types. Here are the table designs so far:

One instructor can have many addresses
One address can have many instructors
Therefore this is a many to many realtionship, and so will be solved with a
junction/intermediate table (called instructorAddresses)

tblInstructors
InstructorID (autonumber, primary key,Long Integer, ref. integrity and
cascade updates)
SpecialID# (text field as this badge number starts with a series of zeroes)
FirstName (text field)
MidName (text field)
LastName (text field)
Sex (or should this be a lookup table? If so, what relationship do I create
and where? I know some of you are saying "well an instructor can only be one
sex at a time" which is true, but what do you do if an instructor is also
transgendered and you need to keep a history of when that instructor was
male, and when they were female)

tblAddresses
AddressID (autonumber,primary key,Long Integer, ref. integrity and cascade
updates)
AddressLine1
AddressLine2
AddressLine3
City
PostalCode
ProvinceStateCounty
Country

tblInstructorAddresses (the junction or intermediate table)
InstructorAddressID (autonumber,primary key,Long Integer, ref. integrity and
cascade updates)
InstructorID (Foreign Key, number, Long integer, Duplicates OK,)
addressID (Foreign Key, number, Long integer, Duplicates OK,)

I have a one to many relationship created from tblInstructors to
tblInstructorAddresses
I have a one to many relationship created from tblAddresses to tbl
InstructorAddresses

Now, I obviously need an addressType Table (Home, Work, Shipping,Billing etc):

tblAddressType
AddressTypeID (autonumber,primary key,Long Integer, ref. integrity and
cascade updates)
AddressType (Text )
FromDate (date)
ToDate (date)

One Address can have many address types (i.e 123 maple Ave can be home and
shipping and billing)

One AddressType has many Addresses (i.e the shipping address type can vary
between a home address or a school address for one instructor depending on
the day)

Am I right that this is a many to many relationship, or am I wrong that
AddressType has many addresses? Am I modeling this correctly as a many to
many relationship?

Billiam

 

[Graham Mandeno]

Hi Billiam

First your question about sex/gender. Sometimes one must ask the question
"why?" I know that it is technically possible for one of your instructors
to start out male and them become female, but before adding significantly to
the complexity of your database design, you need to ask yourself if this
really matters to your business model, and whether it is necessary to keep a
history of the dates of the changes.

If you decide it really is important to track the history then you need a
table InstructorGenderHistory:
ighInstructor (FK to Instructors)
ighGender (*see below)
ighStartDate (date/time)

Note that you don't need an end date. The current gender (your most common
query) is the record with the latest start date and the gender at a given
date in the past is the record with the latest start date BEFORE the given
date.

The actual gender code could be a simple M or F, or it could be a foreign
key to a reference table (lookup tables have bad connotations!). Often it
is useful to have a static table of other gender-related terms, such as
pronouns (he/she, him/her, his/hers, etc).

If you don't want to keep the history then put this gender code field
directly in your Instructors table (plain M/F or FK as required).

On the address question, the correct place for your AddressType FK field is
in the junction table (tblInstructorAddresses).

Say you have two instructors: 1=John and 2=Mary.
They live together at 123 Maple Avenue (AddressID 1)
John works at 456 Elm Street (AddressID 2)
Mary works at 789 Oak Street (AddressID 3)

Your junction table has these fields:
iadID (optional primary key)
iadInstructor (FK to tblInstructors)
iadAddressType (simple code H/W/S/B or FK to tblAddressTypes)
iadAddress (FK to addresses)
iadStartDate (only if you need to keep address change history)

Your data looks like this:
(1) 1 H 1 (John lives at address 1)
(2) 2 H 1 (Mary lives at address 1)
(3) 1 W 2 (John works at address 2)
(4) 2 W 3 (Mary works at address 3)

The address type field is like the gender field - it's up to you whether you
have a reference table or keep it as a simple one-letter code. Whatever you
decide, the history tracking dates DO NOT belong in your reference table,
but in the junction table.

If you want an instructor to have at most ONE address of each type, then
make a unique composite key of iadInstructor and iadAddresstype.

Just a point on cascading relationships - it often makes sense to have
cascading deletes between a main table and a junction table, but it never
makes sense to have cascading updates on an autonumber field, because it is
impossible to update an autonumber field.

 

[Lynn Trapp]

One instructor can have many addresses
One address can have many instructors

This is kind of a peculiar situation. Could you provide some real
world examples of one address with many instructors?

I have a one to many relationship created from tblInstructors to
tblInstructorAddresses
I have a one to many relationship created from tblAddresses to tbl
InstructorAddresses

I think you may be over-normalizing your database. While repeated
fields are usually frowned on, multiple address lines for one
instructor would not typically be out of line.

Lynn Trapp
www.ltcomputerdesigns.com

 

[John... Visio MVP]

I offered to help you on July 24 and you chose to listen to Klatuu instead.
You did not get the help you need in the thread on July 24 so here you are
a week later and no further ahead. What are you going to do if you do not
again get the help you need in this thread?

Steve

Ah, solicitation by intimidation. So is this your new tactic?

These newsgroups are provided by Microsoft for FREE peer to peer support,
not as a source for lining your pockets.

Isn't it time for your disappearing act? For the past few years you have
been trolling the newsgroups and then vanishing near the end of July only to
reappear the following June.

John... Visio MVP

 

[Billiam]

examples in our organization include:
Husband Wife and child, all instructors, at the same home address, and any
combination of work, billing and shipping addresses.
Also, many instructors at one school is another scenario.

Our business rules allow a maximum of 5 street addresses, and most use 4
addresses.

 

[Billiam]

Husband and wife? Father and daughter? Either way, I would keep a one to one
relationship between address and instructor. They may seperate or the
daughter leave home.

John... Visio MVP

Yes you are right, John, we have many groups of families that are
instructors, sharing the same home address, but with any combination of
Billing, shipping, and work addresses. In addition, groups of teachers teach
at a school. Sorry I did not clarify this up front.

And you hit the nail on the head--I will have changes when a spouse or child
leaves the home address, or one of the school addresses change....I was
hoping this could be managed by an effective date??? It is also necessary to
keep a history of the addresses, which was my next question.

 

[Billiam]

Thank you again for your offer, Steve. This free forum, however, suits my
limited budget as a non-profit org. In addition, I think the real point is
that I do have a working non-normalized database, I am here to learn how to
use Access properly so that I can really unleash its huge potential.
Sometimes the best learned lessons are the ones that you have to wrangle with
over time, and I have found that the very knowledgeable and talented people
here ( who do offer their services for free ) to be very helpful,and thought
provoking. I certainly appreciate their efforts and applaud their dedication
to what must sometimes be a very frustrating experience for them dealing with
us newbies!

Billiam

 

[Lynn Trapp]

examples in our organization include:
Husband Wife and child, all instructors, at the same home address, and any
combination of work, billing and shipping addresses.
Also, many instructors at one school is another scenario.

Our business rules allow a maximum of 5 street addresses, and most use 4
addresses.

The possible need for up to 5 street addresses is definitely reaching
the point where you would need to separate them into a separate table.
I would recommend that you use the approach that Graham Mandeno has
outlined for you.
Lynn Trapp
www.ltcomputerdesigns.com

 

[Billiam]

Thank you very, very much for your detailed and timely answer, Graham! I have
made some comments/asked questions as noted below, if you do not mind
sticking with me a bit longer on this?

Hi Billiam

First your question about sex/gender. Sometimes one must ask the question
"why?" I know that it is technically possible for one of your instructors
to start out male and them become female, but before adding significantly to
the complexity of your database design, you need to ask yourself if this
really matters to your business model, and whether it is necessary to keep a
history of the dates of the changes.

It is not a technical possibility but a fact, and I do need to track the
history as the person is submitting to a Government office. While I do not
believe it is likely to happen with another instructor, what do you think is
the best way to handle/link Patrick/Patricia's (not real name) records since
the first name and middle names have also changed, and I have to produce
reports of this person's Instructor Activity as either male or female.

If you decide it really is important to track the history then you need a

table InstructorGenderHistory:
ighInstructor (FK to Instructors)
ighGender (*see below)
ighStartDate (date/time)

If I understand you correctly, we are treating this as a many to many
relationship, and resolving that with the junction table
InstructorGenderHistory.

So one instructor can have many genders (as in Pat/Patricia)
One gender can have many Instructors.

What was/is confusing me is that really an instructor can only be one sex at
a time, so wouldn't that make it a one to one relationship??? And if so, all
I would do is have a gender reference table with a Start date field?

Note that you don't need an end date. The current gender (your most common
query) is the record with the latest start date and the gender at a given
date in the past is the record with the latest start date BEFORE the given
date.

Okay, that makes sense.

The actual gender code could be a simple M or F, or it could be a foreign
key to a reference table (lookup tables have bad connotations!).

Do you mean they sometimes cause confusion between lookup fields and lookup
tables?

Often it > is useful to have a static table of other gender-related terms,
such as

pronouns (he/she, him/her, his/hers, etc).

Yes, this reference table is exactly what I would like, and the personal
pronouns are a great idea.

On the address question, the correct place for your AddressType FK field is
in the junction table (tblInstructorAddresses).

Say you have two instructors: 1=John and 2=Mary.
They live together at 123 Maple Avenue (AddressID 1)
John works at 456 Elm Street (AddressID 2)
Mary works at 789 Oak Street (AddressID 3)

Your junction table has these fields:
iadID (optional primary key)
iadInstructor (FK to tblInstructors)
iadAddressType (simple code H/W/S/B or FK to tblAddressTypes)
iadAddress (FK to addresses)
iadStartDate (only if you need to keep address change history)

Your data looks like this:
(1) 1 H 1 (John lives at address 1)
(2) 2 H 1 (Mary lives at address 1)
(3) 1 W 2 (John works at address 2)
(4) 2 W 3 (Mary works at address 3)

The address type field is like the gender field - it's up to you whether you
have a reference table or keep it as a simple one-letter code. Whatever you
decide, the history tracking dates DO NOT belong in your reference table,
but in the junction table.

If you want an instructor to have at most ONE address of each type, then
make a unique composite key of iadInstructor and iadAddresstype.

Just a point on cascading relationships - it often makes sense to have
cascading deletes between a main table and a junction table, but it never
makes sense to have cascading updates on an autonumber field, because it is
impossible to update an autonumber field.

Sorry, that was an error on my part, as I know an autonumber field cannot be
updated. On this note, I was concerned that since i need to keep track of the
history of addresses if selecting cascade updates/deletions would delete the
history...I am correct that this is not the case, right? If they change their
address that would remain in the InstructorAddress junction table since their
is the unique StartDate field?

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

With the help of this forum, I am trying to correctly design my tables to
be
normalized. I would like to break my design down into sections to work on
just for ease of discussion and for the rest of people trying to figure
this
all out it may make it clearer to them also. I am a beginner in Access and
TRYING HARD to figure it out so that I can properly design a database
instead
of the 55 field non-normlized one I currently use-0

Our non-profit organization has instructors. They may have many addresses
and therefore address types. Here are the table designs so far:

One instructor can have many addresses
One address can have many instructors
Therefore this is a many to many realtionship, and so will be solved with
a
junction/intermediate table (called instructorAddresses)

tblInstructors
InstructorID (autonumber, primary key,Long Integer, ref. integrity and
cascade updates)
SpecialID# (text field as this badge number starts with a series of
zeroes)
FirstName (text field)
MidName (text field)
LastName (text field)
Sex (or should this be a lookup table? If so, what relationship do I
create
and where? I know some of you are saying "well an instructor can only be
one
sex at a time" which is true, but what do you do if an instructor is also
transgendered and you need to keep a history of when that instructor was
male, and when they were female)

tblAddresses
AddressID (autonumber,primary key,Long Integer, ref. integrity and cascade
updates)
AddressLine1
AddressLine2
AddressLine3
City
PostalCode
ProvinceStateCounty
Country

tblInstructorAddresses (the junction or intermediate table)
InstructorAddressID (autonumber,primary key,Long Integer, ref. integrity
and
cascade updates)
InstructorID (Foreign Key, number, Long integer, Duplicates OK,)
addressID (Foreign Key, number, Long integer, Duplicates OK,)

I have a one to many relationship created from tblInstructors to
tblInstructorAddresses
I have a one to many relationship created from tblAddresses to tbl
InstructorAddresses

Now, I obviously need an addressType Table (Home, Work, Shipping,Billing
etc):

tblAddressType
AddressTypeID (autonumber,primary key,Long Integer, ref. integrity and
cascade updates)
AddressType (Text )
FromDate (date)
ToDate (date)

One Address can have many address types (i.e 123 maple Ave can be home and
shipping and billing)

One AddressType has many Addresses (i.e the shipping address type can vary
between a home address or a school address for one instructor depending on
the day)

Am I right that this is a many to many relationship, or am I wrong that
AddressType has many addresses? Am I modeling this correctly as a many to
many relationship?

Billiam

 

[Billiam]

Thanks very much for your input, Lynn...this stuff is so hard to get at
first, but SO important to get! Again, thanks to people like you taking the
time to respond, is so very appreciated!

Billiam

 

[John... Visio MVP]

My point in offering you my help was only to offer you an option of
getting what you want done

WRONG. Your "point" was just another feeble attempt to solicit work from a
group that is set up for FREE peer to peer support.

John...

 

[Graham Mandeno]

Hi Billiam

Following your preference, my answers are inline...

It is not a technical possibility but a fact, and I do need to track the
history as the person is submitting to a Government office.

While I do not
believe it is likely to happen with another instructor, what do you think
is
the best way to handle/link Patrick/Patricia's (not real name) records
since
the first name and middle names have also changed, and I have to produce
reports of this person's Instructor Activity as either male or female.

Hmmm... this adds a whole new dimension. Actually, names can change for
other reasons too, such as marriage. Just thinking by the seat of my pants
here, I'm wondering if it would be better to create a new instructor record
when one of the pieces of basic information (name/gender) changes, with a
DateSuperseded field, so that all historic records can be linked to the
current record. What do you think?

If I understand you correctly, we are treating this as a many to many
relationship, and resolving that with the junction table
InstructorGenderHistory.

So one instructor can have many genders (as in Pat/Patricia)
One gender can have many Instructors.

Yes, that's the idea.

What was/is confusing me is that really an instructor can only be one sex
at
a time, so wouldn't that make it a one to one relationship??? And if so,
all
I would do is have a gender reference table with a Start date field?

The StartDate field cannot be in the reference table, because all the males
don't start being males on the same day!

The "point-in-time" value is ascertained by the StartDate field in the
junction table...

Okay, that makes sense.

.... yes, I see the penny has dropped now

Do you mean they sometimes cause confusion between lookup fields and
lookup
tables?

Yes, reference table/lookup table are the same thing. I just prefer
"reference"

Often it is useful to have a static table of other gender-related terms,
such as

Yes, this reference table is exactly what I would like, and the personal
pronouns are a great idea.

Glad you like it

Sorry, that was an error on my part, as I know an autonumber field cannot
be
updated. On this note, I was concerned that since i need to keep track of
the
history of addresses if selecting cascade updates/deletions would delete
the
history...I am correct that this is not the case, right? If they change
their
address that would remain in the InstructorAddress junction table since
their
is the unique StartDate field?

I sounds like you would not want to cascade deletes. In any case, the
related "many-side" records would olny be deleted if either the
tblInstructors record or the tblAddresses record were deleted.

 

[Billiam]

Thanks for sticking with me Graham! My answers are inline.

Hmmm... this adds a whole new dimension. Actually, names can change for
other reasons too, such as marriage. Just thinking by the seat of my pants
here, I'm wondering if it would be better to create a new instructor record
when one of the pieces of basic information (name/gender) changes, with a
DateSuperseded field, so that all historic records can be linked to the
current record. What do you think?

Graham, I think that sounds great, but before I start changing things I also
want you to be aware of another problem I have. Some Instructors wish to have
their information posted on our website (about 60 percent of them). The
information that is posted is: First and Last name, Phone#, Cities they teach
in, an e-mail address, and a website link, and their qualifications. (BTW, I
have tblQualifications in a many to many with tblnstructors and a junction
table InstructorQualifications). Here is my problem...instructors must be
entered in by their Given names, and as you probably know, not everyone uses
their given name as their first name. Also, the cities entered in for address
are not necessarily where the instructor teaches. As you can probably see,
then, it is difficult to build the report to export to the website of all the
instructors that wish to be on the public website.

In my non-normalized database, I actually had a different table called
"PublicInfo", but obviously there is a lot of stuff being repeated doing it
that way. I know it sounds ridiculous, but should I have a first names table?
If I have a seperate table of cities, could I somehow use a kind of multiple
combo box to choose a maximum of three cities from the city table for each
instructor that wishes to be on the website , on an Instructor Info form? Is
there an easier way to solve this...as I said, I am really new to access, and
could use your experience, Graham!!!

Many, many Thanks,

Billiam

 

[Graham Mandeno]

Hi Billiam

What I usually do is add a PreferredName field. Ninety percent of the time
this is blank, in which case FirstName is used. It handles the problem of
diminutives as well as people who are known by a middle name.

You can go overboard with normalising. I would not pull a field with a
repeating value out into another table unless it truly represents another
entity that could be used for grouping and selection. Even though you might
have hundreds of records in a Persons table with FirstName "John", this
common attribute does not relate these records to each other in any logical
way, so there is no sense in having a separate table of FirstNames.

However, the cities in which an instructor teaches is an attribute that
could well be used to relate instructor records, so it makes sense to have a
separate table. It should be many-to-many, by the way, just like
Qualifications. You don't want to limit an instructor to three cities
because of the restriction in your design.

I have written a sample database demonstrating an easy way to manage
many-to-many relationships, using listboxes and combo boxes on a form. My
colleague, Ken Snell, has kindly put this up on his website at
http://www.accessmvp.com/KDSnell/SampleDBs.htm. It is near the bottom of
the page, in the section "Easy Maintenance of "Many-To-Many" Data with a
Form".

I suggest you download it and see if it will serve your purpose. Don't
worry too much about the code in the class module named "MtoMListHandler" -
just look at the forms.

Then you can import MtoMListHandler into your database and set up your forms
in a similar way.

 

[Billiam]

Thanks again for your time and advice. I will definately look at the Ken
Snell database, thanks for that link. On the topic of Address structure, I am
wondering if the following is the correct way to design the following as i
seem to be getting overwhelmed a bit by the realtionships:

City, ZipPostalCode, ProvinceStateCounty, Country

One City can have many ZipPostalCode
One ZipPostalCode can have many cities
Therefore a many to many relationship which I would solve using a junction
table Called CityZipPostalCode.

I am really unsure what to do next, Graham. Do I need to define a
relationship further between city and StateProvinceCounty, as 1 city can be
in more than one StateProvinceCounty and one StateProvinceCounty can have
many cities...If I do, is this another junction table???

Also, one city can be in many countries and one country can have many
cities???
Finally, how and where do I relate StateProvinceCounty to the Countries
table...
as i do not know if many StateProvinceCounty are in many Countries (should
have studued Geography more ,<g> see ideas spinning around in mostly empty
cranial cavity causing what little brain there is *PAIN*

Note: I do search on city quite a bit, so figure it is important to set this
up right, finally!

Billiam

Hi Billiam

What I usually do is add a PreferredName field. Ninety percent of the time
this is blank, in which case FirstName is used. It handles the problem of
diminutives as well as people who are known by a middle name.

You can go overboard with normalising. I would not pull a field with a
repeating value out into another table unless it truly represents another
entity that could be used for grouping and selection. Even though you might
have hundreds of records in a Persons table with FirstName "John", this
common attribute does not relate these records to each other in any logical
way, so there is no sense in having a separate table of FirstNames.

However, the cities in which an instructor teaches is an attribute that
could well be used to relate instructor records, so it makes sense to have a
separate table. It should be many-to-many, by the way, just like
Qualifications. You don't want to limit an instructor to three cities
because of the restriction in your design.

I have written a sample database demonstrating an easy way to manage
many-to-many relationships, using listboxes and combo boxes on a form. My
colleague, Ken Snell, has kindly put this up on his website at
http://www.accessmvp.com/KDSnell/SampleDBs.htm. It is near the bottom of
the page, in the section "Easy Maintenance of "Many-To-Many" Data with a
Form".

I suggest you download it and see if it will serve your purpose. Don't
worry too much about the code in the class module named "MtoMListHandler" -
just look at the forms.

Then you can import MtoMListHandler into your database and set up your forms
in a similar way.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for sticking with me Graham! My answers are inline.



Graham, I think that sounds great, but before I start changing things I
also
want you to be aware of another problem I have. Some Instructors wish to
have
their information posted on our website (about 60 percent of them). The
information that is posted is: First and Last name, Phone#, Cities they
teach
in, an e-mail address, and a website link, and their qualifications. (BTW,
I
have tblQualifications in a many to many with tblnstructors and a junction
table InstructorQualifications). Here is my problem...instructors must be
entered in by their Given names, and as you probably know, not everyone
uses
their given name as their first name. Also, the cities entered in for
address
are not necessarily where the instructor teaches. As you can probably see,
then, it is difficult to build the report to export to the website of all
the
instructors that wish to be on the public website.

In my non-normalized database, I actually had a different table called
"PublicInfo", but obviously there is a lot of stuff being repeated doing
it
that way. I know it sounds ridiculous, but should I have a first names
table?
If I have a seperate table of cities, could I somehow use a kind of
multiple
combo box to choose a maximum of three cities from the city table for each
instructor that wishes to be on the website , on an Instructor Info form?
Is
there an easier way to solve this...as I said, I am really new to access,
and
could use your experience, Graham!!!

Many, many Thanks,

Billiam

 

[Graham Mandeno]

Hi Billiam

Like I said, you can go overboard with normalisation!

As far as I know, a city can NOT be in two countries. The London in England
is not the same as the London in Ontario, Canada. They bear no more
relationship to one another that my uncle George to George Bush. So, if you
have a table of Cities, London would have two records, distinguishable by
other fields such as StateProvinceCounty and/or Country.

You can also fall into the trap of making a box that requires many objects
to be distorted in order to fit it. For example, some countries do not have
any states/provinces/counties. Also, some countries have a concept of a
suburb within a city (while smaller towns don't have suburbs) and other
countries have the concept of a suburb or town as being the "city" within a
state.

It's difficult to provide a definitive answer! I can only caution you
against (a) over-normalising and (b) creating a design that doesn't fit all
cases.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks again for your time and advice. I will definately look at the Ken
Snell database, thanks for that link. On the topic of Address structure, I
am
wondering if the following is the correct way to design the following as i
seem to be getting overwhelmed a bit by the realtionships:

City, ZipPostalCode, ProvinceStateCounty, Country

One City can have many ZipPostalCode
One ZipPostalCode can have many cities
Therefore a many to many relationship which I would solve using a junction
table Called CityZipPostalCode.

I am really unsure what to do next, Graham. Do I need to define a
relationship further between city and StateProvinceCounty, as 1 city can
be
in more than one StateProvinceCounty and one StateProvinceCounty can have
many cities...If I do, is this another junction table???

Also, one city can be in many countries and one country can have many
cities???
Finally, how and where do I relate StateProvinceCounty to the Countries
table...
as i do not know if many StateProvinceCounty are in many Countries
(should
have studued Geography more ,<g> see ideas spinning around in mostly empty
cranial cavity causing what little brain there is *PAIN*

Note: I do search on city quite a bit, so figure it is important to set
this
up right, finally!

Billiam

Hi Billiam

What I usually do is add a PreferredName field. Ninety percent of the
time
this is blank, in which case FirstName is used. It handles the problem
of
diminutives as well as people who are known by a middle name.

You can go overboard with normalising. I would not pull a field with a
repeating value out into another table unless it truly represents another
entity that could be used for grouping and selection. Even though you
might
have hundreds of records in a Persons table with FirstName "John", this
common attribute does not relate these records to each other in any
logical
way, so there is no sense in having a separate table of FirstNames.

However, the cities in which an instructor teaches is an attribute that
could well be used to relate instructor records, so it makes sense to
have a
separate table. It should be many-to-many, by the way, just like
Qualifications. You don't want to limit an instructor to three cities
because of the restriction in your design.

I have written a sample database demonstrating an easy way to manage
many-to-many relationships, using listboxes and combo boxes on a form.
My
colleague, Ken Snell, has kindly put this up on his website at
http://www.accessmvp.com/KDSnell/SampleDBs.htm. It is near the bottom of
the page, in the section "Easy Maintenance of "Many-To-Many" Data with a
Form".

I suggest you download it and see if it will serve your purpose. Don't
worry too much about the code in the class module named
"MtoMListHandler" -
just look at the forms.

Then you can import MtoMListHandler into your database and set up your
forms
in a similar way.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for sticking with me Graham! My answers are inline.

:
Hmmm... this adds a whole new dimension. Actually, names can change
for
other reasons too, such as marriage. Just thinking by the seat of my
pants
here, I'm wondering if it would be better to create a new instructor
record
when one of the pieces of basic information (name/gender) changes,
with a
DateSuperseded field, so that all historic records can be linked to
the
current record. What do you think?

Graham, I think that sounds great, but before I start changing things I
also
want you to be aware of another problem I have. Some Instructors wish
to
have
their information posted on our website (about 60 percent of them). The
information that is posted is: First and Last name, Phone#, Cities they
teach
in, an e-mail address, and a website link, and their qualifications.
(BTW,
I
have tblQualifications in a many to many with tblnstructors and a
junction
table InstructorQualifications). Here is my problem...instructors must
be
entered in by their Given names, and as you probably know, not everyone
uses
their given name as their first name. Also, the cities entered in for
address
are not necessarily where the instructor teaches. As you can probably
see,
then, it is difficult to build the report to export to the website of
all
the
instructors that wish to be on the public website.

In my non-normalized database, I actually had a different table called
"PublicInfo", but obviously there is a lot of stuff being repeated
doing
it
that way. I know it sounds ridiculous, but should I have a first names
table?
If I have a seperate table of cities, could I somehow use a kind of
multiple
combo box to choose a maximum of three cities from the city table for
each
instructor that wishes to be on the website , on an Instructor Info
form?
Is
there an easier way to solve this...as I said, I am really new to
access,
and
could use your experience, Graham!!!

Many, many Thanks,

Billiam

 

[Billiam]

Hi Graham,

Thanks again for the great advice. I had read somewhere else that a city
"could be in 2 different countries, and so was confused...thanks for clearing
that up.

I really appreciate you sticking with me on this, and for your timely
responses! I will keep plugging away at it. Have a great day,

Billiam

Hi Billiam

Like I said, you can go overboard with normalisation!

As far as I know, a city can NOT be in two countries. The London in England
is not the same as the London in Ontario, Canada. They bear no more
relationship to one another that my uncle George to George Bush. So, if you
have a table of Cities, London would have two records, distinguishable by
other fields such as StateProvinceCounty and/or Country.

You can also fall into the trap of making a box that requires many objects
to be distorted in order to fit it. For example, some countries do not have
any states/provinces/counties. Also, some countries have a concept of a
suburb within a city (while smaller towns don't have suburbs) and other
countries have the concept of a suburb or town as being the "city" within a
state.

It's difficult to provide a definitive answer! I can only caution you
against (a) over-normalising and (b) creating a design that doesn't fit all
cases.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks again for your time and advice. I will definately look at the Ken
Snell database, thanks for that link. On the topic of Address structure, I
am
wondering if the following is the correct way to design the following as i
seem to be getting overwhelmed a bit by the realtionships:

City, ZipPostalCode, ProvinceStateCounty, Country

One City can have many ZipPostalCode
One ZipPostalCode can have many cities
Therefore a many to many relationship which I would solve using a junction
table Called CityZipPostalCode.

I am really unsure what to do next, Graham. Do I need to define a
relationship further between city and StateProvinceCounty, as 1 city can
be
in more than one StateProvinceCounty and one StateProvinceCounty can have
many cities...If I do, is this another junction table???

Also, one city can be in many countries and one country can have many
cities???
Finally, how and where do I relate StateProvinceCounty to the Countries
table...
as i do not know if many StateProvinceCounty are in many Countries
(should
have studued Geography more ,<g> see ideas spinning around in mostly empty
cranial cavity causing what little brain there is *PAIN*

Note: I do search on city quite a bit, so figure it is important to set
this
up right, finally!

Billiam

Hi Billiam

What I usually do is add a PreferredName field. Ninety percent of the
time
this is blank, in which case FirstName is used. It handles the problem
of
diminutives as well as people who are known by a middle name.

You can go overboard with normalising. I would not pull a field with a
repeating value out into another table unless it truly represents another
entity that could be used for grouping and selection. Even though you
might
have hundreds of records in a Persons table with FirstName "John", this
common attribute does not relate these records to each other in any
logical
way, so there is no sense in having a separate table of FirstNames.

However, the cities in which an instructor teaches is an attribute that
could well be used to relate instructor records, so it makes sense to
have a
separate table. It should be many-to-many, by the way, just like
Qualifications. You don't want to limit an instructor to three cities
because of the restriction in your design.

I have written a sample database demonstrating an easy way to manage
many-to-many relationships, using listboxes and combo boxes on a form.
My
colleague, Ken Snell, has kindly put this up on his website at
http://www.accessmvp.com/KDSnell/SampleDBs.htm. It is near the bottom of
the page, in the section "Easy Maintenance of "Many-To-Many" Data with a
Form".

I suggest you download it and see if it will serve your purpose. Don't
worry too much about the code in the class module named
"MtoMListHandler" -
just look at the forms.

Then you can import MtoMListHandler into your database and set up your
forms
in a similar way.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


Thanks for sticking with me Graham! My answers are inline.

:
Hmmm... this adds a whole new dimension. Actually, names can change
for
other reasons too, such as marriage. Just thinking by the seat of my
pants
here, I'm wondering if it would be better to create a new instructor
record
when one of the pieces of basic information (name/gender) changes,
with a
DateSuperseded field, so that all historic records can be linked to
the
current record. What do you think?

Graham, I think that sounds great, but before I start changing things I
also
want you to be aware of another problem I have. Some Instructors wish
to
have
their information posted on our website (about 60 percent of them). The
information that is posted is: First and Last name, Phone#, Cities they
teach
in, an e-mail address, and a website link, and their qualifications.
(BTW,
I
have tblQualifications in a many to many with tblnstructors and a
junction
table InstructorQualifications). Here is my problem...instructors must
be
entered in by their Given names, and as you probably know, not everyone
uses
their given name as their first name. Also, the cities entered in for
address
are not necessarily where the instructor teaches. As you can probably
see,
then, it is difficult to build the report to export to the website of
all
the
instructors that wish to be on the public website.

In my non-normalized database, I actually had a different table called
"PublicInfo", but obviously there is a lot of stuff being repeated
doing
it
that way. I know it sounds ridiculous, but should I have a first names
table?
If I have a seperate table of cities, could I somehow use a kind of
multiple
combo box to choose a maximum of three cities from the city table for
each
instructor that wishes to be on the website , on an Instructor Info
form?
Is
there an easier way to solve this...as I said, I am really new to
access,
and
could use your experience, Graham!!!

Many, many Thanks,

Billiam