Address Design Structure

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

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.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


"Billiam" wrote in message
...
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

On Thu, 31 Jul 2008 12:35:00 -0700, Billiam
wrote:


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

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


"Billiam" wrote in message
...
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

"Steve" wrote in message
m...
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

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.



"Lynn Trapp" wrote:

On Thu, 31 Jul 2008 12:35:00 -0700, Billiam
wrote:


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" wrote:


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.

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

"Steve" wrote:

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


"Billiam" wrote in message
...
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

On Fri, 1 Aug 2008 08:02:10 -0700, Billiam
wrote:

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

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?

"Graham Mandeno" wrote:

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


"Billiam" wrote in message
...
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