Database Design Normalization help

Ok Duane, here is the link:
http://www.box.net/shared/z3wal6ncok

Sorry to be such a pain--I really want to make sure I am using the right
type of relationships, in addition to the table layouts. After your
reccomendations, I will make changes and post what I've learned so others get
the benfits well.
"Duane Hookom" wrote:

I prefer to keep news group discussions public. Consider posting a jpg to a
free file server like http://www.box.net/

--
Duane Hookom
Microsoft Access MVP


"Billiam" wrote:

Could I send you an e-mail invitation to view the relationship report on a
Microsoft Office Live Workspace?

"Duane Hookom" wrote:

You shouldn't attempt to attach files to messages in public forums/news
groups. You can find sites on the web that allow you to post files.
--
Duane Hookom
Microsoft Access MVP


"Billiam" wrote:

Since there are many tables and relationships, I was wondering if there was a
way to attach a relationship report in .pdf for you to see?

"Duane Hookom" wrote:

You should type in your tables and fields as well as describe your
relationships in a post to this news group.

--
Duane Hookom
Microsoft Access MVP


"Billiam" wrote:

I am a new user and have set up a database and relationships in Access 2007.
I was wondering if anyone could have a look at my design and offer any help
or suggestions, as I am finding it very difficult to comprehend relationships
despite all my looking and reading. If anyone is willing to help, I can
e-mail the database, or perhaps someone could let me know another way that
might be safer for someone to see what I have done in order to critique it or
offer help?
Thank you for any help as this is making my brain hurt!

Should I start a new thread so it is easier to follow?

"Duane Hookom" wrote:

I prefer to keep news group discussions public. Consider posting a jpg to a
free file server like http://www.box.net/

--
Duane Hookom
Microsoft Access MVP


"Billiam" wrote:

Could I send you an e-mail invitation to view the relationship report on a
Microsoft Office Live Workspace?

"Duane Hookom" wrote:

You shouldn't attempt to attach files to messages in public forums/news
groups. You can find sites on the web that allow you to post files.
--
Duane Hookom
Microsoft Access MVP


"Billiam" wrote:

Since there are many tables and relationships, I was wondering if there was a
way to attach a relationship report in .pdf for you to see?

"Duane Hookom" wrote:

You should type in your tables and fields as well as describe your
relationships in a post to this news group.

--
Duane Hookom
Microsoft Access MVP


"Billiam" wrote:

I am a new user and have set up a database and relationships in Access 2007.
I was wondering if anyone could have a look at my design and offer any help
or suggestions, as I am finding it very difficult to comprehend relationships
despite all my looking and reading. If anyone is willing to help, I can
e-mail the database, or perhaps someone could let me know another way that
might be safer for someone to see what I have done in order to critique it or
offer help?
Thank you for any help as this is making my brain hurt!

Stay in this thread as long as the question doesn't change.

I would question any 1 to 1 relationships and consider place the fields into
one table. Are the HumanResources and Instructors tables 1 to 1? How about
the Status table to Instructors? Do you need to keep a history of statuses or
just the current status?

Your InstructorAddresses and Addresses tables look like they contain
redundant information ie: CountryID and Country. I generally don't create
lookup tables for cities, postal codes, countries, regions, etc.

If a bunch of instructors don't share the same phone number, I wouldn't
create a separate phones table. I would just place the phone numbers in the
InstructorPhoneNumbers table.

Can an instructor have more than one position?
--
Duane Hookom
Microsoft Access MVP


"Billiam" wrote:

Ok Duane, here is the link:
http://www.box.net/shared/z3wal6ncok

Sorry to be such a pain--I really want to make sure I am using the right
type of relationships, in addition to the table layouts. After your
reccomendations, I will make changes and post what I've learned so others get
the benfits well.
"Duane Hookom" wrote:

I prefer to keep news group discussions public. Consider posting a jpg to a
free file server like http://www.box.net/

--
Duane Hookom
Microsoft Access MVP


"Billiam" wrote:

Could I send you an e-mail invitation to view the relationship report on a
Microsoft Office Live Workspace?

"Duane Hookom" wrote:

You shouldn't attempt to attach files to messages in public forums/news
groups. You can find sites on the web that allow you to post files.
--
Duane Hookom
Microsoft Access MVP


"Billiam" wrote:

Since there are many tables and relationships, I was wondering if there was a
way to attach a relationship report in .pdf for you to see?

"Duane Hookom" wrote:

You should type in your tables and fields as well as describe your
relationships in a post to this news group.

--
Duane Hookom
Microsoft Access MVP


"Billiam" wrote:

I am a new user and have set up a database and relationships in Access 2007.
I was wondering if anyone could have a look at my design and offer any help
or suggestions, as I am finding it very difficult to comprehend relationships
despite all my looking and reading. If anyone is willing to help, I can
e-mail the database, or perhaps someone could let me know another way that
might be safer for someone to see what I have done in order to critique it or
offer help?
Thank you for any help as this is making my brain hurt!

"Duane Hookom" wrote:


I would question any 1 to 1 relationships and consider place the fields into
one table. Are the HumanResources and Instructors tables 1 to 1? How about
the Status table to Instructors?

I am not sure if I am producing the relationships correctly...I will provide
my business rules at the end of your replies, and perhaps this will clarify
what I need to do as far as setting up my tables. I am not sure if I am
utilizing/setting up foreign keys and junction tables...

Do you need to keep a history of statuses or just the current status?

I need both the Current status AND a history of statuses.
I assume I need to put FromDate and ToDate fields in the Status table?

I also need to keep track of previous phone numbers, addresses and email/web
info. Can you recommend what to do here?

Your InstructorAddresses and Addresses tables look like they contain
redundant information ie: CountryID and Country.

My thinking was this is a many:many relationship based on the following Bus.
rule:
One Instructor has many addresses
One address has many Instructors (Husband, wife and sometimes son/daughter)
I made a junction table (InstructorAddresses) and thought I needed the
Country ID as a foreign key in the junction table InstructorAddresses?

I generally don't create lookup tables for cities, postal codes, countries, regions, etc.

I was wondering if I am tryng to over normalize these items. I often have to
search on City, and regions typically do not change, but I have heard some
talk lately of possibly revamping which region applies to which address and
also to which region description, and so I thought it might be easier to
break these items up in order to change them more easily. Also, I thought it
would not hurt to have the database prepared should we become more global and
so I added a seperate Country table...it seemed to me I read something about
having these in seperate tables as they were transitively dependent on the
key, or something like that...will this design slow things down too much
building the junction table CityPostalCode? It seems to me I remember reading
about a way to help this by having the sort not begn until a certain number
of letters appeared (for example, no sorting until "New York" was entered. I
do need your advice on this.



If a bunch of instructors don't share the same phone number, I wouldn't
create a separate phones table. I would just place the phone numbers in the
InstructorPhoneNumbers table.

So I do not need the many:many relationship here, I can just use a 1:to many
relationship between Instructors and instructors phones? BTW, About 20% of
Instructors do share phone numbers. Could you explain why you wouldn't use a
seperate phones table so I might understand a little better what I am doing?
I think you will probaly say that too many tables will become difficult to
manage with queries and reports?

Since there is a possibility that this database could be asked to be
extended to accept sales of a few items, and therefore inventory (about 30
items), do you have any reccomendations for keeping the design flexible
enough to handle the possibilty of Customers, Orders, CustomerOrders and
adding them in with their multitude of address types?

Can an instructor have more than one position?

Yes, they can be on the executive, Board, Auditors etc.

Finally, Duane, I am not sure how to handle the following problem. I use the
database to produse a report of Instructors who wish to have their Name,
City, Phone Number, E-mail, website and which qualifications they have
available to the public. A person can click on a particular area code on the
website, and then a list of instructors in that areacode is provided (a .pdf
report uploaded to the website) Here is the problem...To complicate things,
the Instructors given names are entered into the database, not their
nicknames or known by names. So I need to pull, for example, all Instructors
who are Active Status, include their preferred first name--not their given
name as entered in the database,(so do I add this as a field in the
Instructor Table as PrefferedFirstName?) LastName, City--This is my other
problem---Instructors can list up to three cities to be included on the
website (surrounding areas), and I am only tracking one city per address so
not sure how to handle that...Phone number (could be home or business or
cell--this is the instructors personal preference ) finally qualifications.
So how do I handle tagging that this first name, these cities and this phone
number are "Public"...do I add public as an address type, city as a CityType,
? And what do I do about PreferredFirstName?

Thank you for any help you can provide! I have really been wrestling with
this for awhile. I am currently using a flat table design as I got the job of
doing the database for a non-profit organization and had no experience...live
and learn!
Billiam
--
Duane Hookom
Microsoft Access MVP


"Billiam" wrote:

Ok Duane, here is the link:
http://www.box.net/shared/z3wal6ncok

Sorry to be such a pain--I really want to make sure I am using the right
type of relationships, in addition to the table layouts. After your
reccomendations, I will make changes and post what I've learned so others get
the benfits well.
"Duane Hookom" wrote:

I prefer to keep news group discussions public. Consider posting a jpg to a
free file server like http://www.box.net/

--
Duane Hookom
Microsoft Access MVP


"Billiam" wrote:

Could I send you an e-mail invitation to view the relationship report on a
Microsoft Office Live Workspace?

"Duane Hookom" wrote:

You shouldn't attempt to attach files to messages in public forums/news
groups. You can find sites on the web that allow you to post files.
--
Duane Hookom
Microsoft Access MVP


"Billiam" wrote:

Since there are many tables and relationships, I was wondering if there was a
way to attach a relationship report in .pdf for you to see?

"Duane Hookom" wrote:

You should type in your tables and fields as well as describe your
relationships in a post to this news group.

--
Duane Hookom
Microsoft Access MVP


"Billiam" wrote:

I am a new user and have set up a database and relationships in Access 2007.
I was wondering if anyone could have a look at my design and offer any help
or suggestions, as I am finding it very difficult to comprehend relationships
despite all my looking and reading. If anyone is willing to help, I can
e-mail the database, or perhaps someone could let me know another way that
might be safer for someone to see what I have done in order to critique it or
offer help?
Thank you for any help as this is making my brain hurt!

Steve, you are wrong! Billiam is getting good, professional help here and
he is not paying for it, which is the way these forums are designed to work.

If you want paid work and don't want to fit in here, I suggest you get
yourself down to the Labour Exchange or put on a sandwich board and walk
around the streets selling your CDs of stolen software.
"Steve" wrote in message
m...
My offer to help you is sincere! You are not getting a help in the
newsgroup and my offer is open if you are interested.

Steve


"Billiam" wrote in message
...
So I have seen in several posts :-]

"Klatuu" wrote:

Do not contact Steve.
--
Dave Hargis, Microsoft Access MVP


"Billiam" wrote:

I am a new user and have set up a database and relationships in Access
2007.
I was wondering if anyone could have a look at my design and offer any
help
or suggestions, as I am finding it very difficult to comprehend
relationships
despite all my looking and reading. If anyone is willing to help, I
can
e-mail the database, or perhaps someone could let me know another way
that
might be safer for someone to see what I have done in order to
critique it or
offer help?
Thank you for any help as this is making my brain hurt!

"Steve" wrote in message
m...
My offer to help you is sincere! You are not getting a help in the
newsgroup and my offer is open if you are interested.

Steve


It may be sincere, but it is not appropriate and as has been proven many
times in the past, of dubious value.

John... Visio MVP

Somehow the following response from me was placed before my earlier response
in the thread (about starting a new thread) here it is again in case you did
not receive it:

"Duane Hookom" wrote:

Stay in this thread as long as the question doesn't change.

I would question any 1 to 1 relationships and consider place the fields into
one table. Are the HumanResources and Instructors tables 1 to 1? How about
the Status table to Instructors? Do you need to keep a history of statuses or
just the current status?

Your InstructorAddresses and Addresses tables look like they contain
redundant information ie: CountryID and Country. I generally don't create
lookup tables for cities, postal codes, countries, regions, etc.

If a bunch of instructors don't share the same phone number, I wouldn't
create a separate phones table. I would just place the phone numbers in the
InstructorPhoneNumbers table.

Can an instructor have more than one position?
--
Duane Hookom
Microsoft Access MVP


"Billiam" wrote:

Ok Duane, here is the link:
http://www.box.net/shared/z3wal6ncok

Sorry to be such a pain--I really want to make sure I am using the right
type of relationships, in addition to the table layouts. After your
reccomendations, I will make changes and post what I've learned so others get
the benfits well.
"Duane Hookom" wrote:

I prefer to keep news group discussions public. Consider posting a jpg to a
free file server like http://www.box.net/

--
Duane Hookom
Microsoft Access MVP


"Billiam" wrote:

Could I send you an e-mail invitation to view the relationship report on a
Microsoft Office Live Workspace?

"Duane Hookom" wrote:

You shouldn't attempt to attach files to messages in public forums/news
groups. You can find sites on the web that allow you to post files.
--
Duane Hookom
Microsoft Access MVP


"Billiam" wrote:

Since there are many tables and relationships, I was wondering if there was a
way to attach a relationship report in .pdf for you to see?

"Duane Hookom" wrote:

You should type in your tables and fields as well as describe your
relationships in a post to this news group.

--
Duane Hookom
Microsoft Access MVP


"Billiam" wrote:

I am a new user and have set up a database and relationships in Access 2007.
I was wondering if anyone could have a look at my design and offer any help
or suggestions, as I am finding it very difficult to comprehend relationships
despite all my looking and reading. If anyone is willing to help, I can
e-mail the database, or perhaps someone could let me know another way that
might be safer for someone to see what I have done in order to critique it or
offer help?
Thank you for any help as this is making my brain hurt!

It looks like your status table contains only a single record per instructor.
You would need to add at least one date field to note when the status
changed. I would change the name to tblInstructorStatusHistory and create a
separate lookup table for statuses.

If you want to keep a history of addresses, your table structures would be
similar and include a date field. If you want to create a combo box for
selecting a country, I wouldn't necessarily use a CountryID field unless you
had a meaningful Country ID. In the US, we have State names and State
Abbreviations the could be used on a lookup table.

Having the lookup table for regions, cities, and countries would not affect
the performance very much as long as there were proper indexes.
--
Duane Hookom
Microsoft Access MVP


"Billiam" wrote:

"Duane Hookom" wrote:


I would question any 1 to 1 relationships and consider place the fields into
one table. Are the HumanResources and Instructors tables 1 to 1? How about
the Status table to Instructors?

I am not sure if I am producing the relationships correctly...I will provide
my business rules at the end of your replies, and perhaps this will clarify
what I need to do as far as setting up my tables. I am not sure if I am
utilizing/setting up foreign keys and junction tables...

Do you need to keep a history of statuses or just the current status?

I need both the Current status AND a history of statuses.
I assume I need to put FromDate and ToDate fields in the Status table?

I also need to keep track of previous phone numbers, addresses and email/web
info. Can you recommend what to do here?

Your InstructorAddresses and Addresses tables look like they contain
redundant information ie: CountryID and Country.

My thinking was this is a many:many relationship based on the following Bus.
rule:
One Instructor has many addresses
One address has many Instructors (Husband, wife and sometimes son/daughter)
I made a junction table (InstructorAddresses) and thought I needed the
Country ID as a foreign key in the junction table InstructorAddresses?

I generally don't create lookup tables for cities, postal codes, countries, regions, etc.

I was wondering if I am tryng to over normalize these items. I often have to
search on City, and regions typically do not change, but I have heard some
talk lately of possibly revamping which region applies to which address and
also to which region description, and so I thought it might be easier to
break these items up in order to change them more easily. Also, I thought it
would not hurt to have the database prepared should we become more global and
so I added a seperate Country table...it seemed to me I read something about
having these in seperate tables as they were transitively dependent on the
key, or something like that...will this design slow things down too much
building the junction table CityPostalCode? It seems to me I remember reading
about a way to help this by having the sort not begn until a certain number
of letters appeared (for example, no sorting until "New York" was entered. I
do need your advice on this.



If a bunch of instructors don't share the same phone number, I wouldn't
create a separate phones table. I would just place the phone numbers in the
InstructorPhoneNumbers table.

So I do not need the many:many relationship here, I can just use a 1:to many
relationship between Instructors and instructors phones? BTW, About 20% of
Instructors do share phone numbers. Could you explain why you wouldn't use a
seperate phones table so I might understand a little better what I am doing?
I think you will probaly say that too many tables will become difficult to
manage with queries and reports?

Since there is a possibility that this database could be asked to be
extended to accept sales of a few items, and therefore inventory (about 30
items), do you have any reccomendations for keeping the design flexible
enough to handle the possibilty of Customers, Orders, CustomerOrders and
adding them in with their multitude of address types?

Can an instructor have more than one position?

Yes, they can be on the executive, Board, Auditors etc.

Finally, Duane, I am not sure how to handle the following problem. I use the
database to produse a report of Instructors who wish to have their Name,
City, Phone Number, E-mail, website and which qualifications they have
available to the public. A person can click on a particular area code on the
website, and then a list of instructors in that areacode is provided (a .pdf
report uploaded to the website) Here is the problem...To complicate things,
the Instructors given names are entered into the database, not their
nicknames or known by names. So I need to pull, for example, all Instructors
who are Active Status, include their preferred first name--not their given
name as entered in the database,(so do I add this as a field in the
Instructor Table as PrefferedFirstName?) LastName, City--This is my other
problem---Instructors can list up to three cities to be included on the
website (surrounding areas), and I am only tracking one city per address so
not sure how to handle that...Phone number (could be home or business or
cell--this is the instructors personal preference ) finally qualifications.
So how do I handle tagging that this first name, these cities and this phone
number are "Public"...do I add public as an address type, city as a CityType,
? And what do I do about PreferredFirstName?

Thank you for any help you can provide! I have really been wrestling with
this for awhile. I am currently using a flat table design as I got the job of
doing the database for a non-profit organization and had no experience...live
and learn!
Billiam
--
Duane Hookom
Microsoft Access MVP


"Billiam" wrote:

Ok Duane, here is the link:
http://www.box.net/shared/z3wal6ncok

Sorry to be such a pain--I really want to make sure I am using the right
type of relationships, in addition to the table layouts. After your
reccomendations, I will make changes and post what I've learned so others get
the benfits well.
"Duane Hookom" wrote:

I prefer to keep news group discussions public. Consider posting a jpg to a
free file server like http://www.box.net/

--
Duane Hookom
Microsoft Access MVP


"Billiam" wrote:

Could I send you an e-mail invitation to view the relationship report on a
Microsoft Office Live Workspace?

"Duane Hookom" wrote:

You shouldn't attempt to attach files to messages in public forums/news
groups. You can find sites on the web that allow you to post files.
--
Duane Hookom
Microsoft Access MVP


"Billiam" wrote:

Since there are many tables and relationships, I was wondering if there was a
way to attach a relationship report in .pdf for you to see?

"Duane Hookom" wrote:

You should type in your tables and fields as well as describe your
relationships in a post to this news group.

--
Duane Hookom
Microsoft Access MVP


"Billiam" wrote:

I am a new user and have set up a database and relationships in Access 2007.
I was wondering if anyone could have a look at my design and offer any help
or suggestions, as I am finding it very difficult to comprehend relationships
despite all my looking and reading. If anyone is willing to help, I can
e-mail the database, or perhaps someone could let me know another way that
might be safer for someone to see what I have done in order to critique it or
offer help?
Thank you for any help as this is making my brain hurt!

"Duane Hookom" wrote:

It looks like your status table contains only a single record per instructor.
You would need to add at least one date field to note when the status
changed. I would change the name to tblInstructorStatusHistory and create a
separate lookup table for statuses.

To make sure I understand you correctly, I would make the following Tables:

tblStatus
StatusID (Primary key, autonumber, long integer, referential integrity
enforced)
Status (Active, On-Leave, suspended etc)

tblInstructorStatusHistory
InstructorStatusID (Primary key, autonumber, long integer, ref. integrity
enforced)
InstructorID (Foreign key, number, long integer, indexed NO duplicates)
StatusID (Foreign key, number, long integer, indexed NO duplicates)
DateFrom
DateTo

As far as the relationship between these tables, I am not sure what it would
be:
One Instructor has One status at a time, possibly many over time
One status can apply to many instructors

So I think it is best to model it as a many to many relationship
tblInstructors and tblStatus with a junction of tblInstructorStatusHistory???

If you want to keep a history of addresses, your table structures would be
similar and include a date field. If you want to create a combo box for
selecting a country, I wouldn't necessarily use a CountryID field unless you
had a meaningful Country ID.

I am using the ISU country number (ie USA=1, Canada=2 etc) as the CountryID

In the US, we have State names and State
Abbreviations the could be used on a lookup table.

Having the lookup table for regions, cities, and countries would not affect
the performance very much as long as there were proper indexes.

Duane, by proper indexes, do you mean I choose Index NO Duplicates in
addition to ref. integrity?

Do lookup tables generate the 1 to 1 relationship type, or id that a result
from choosing no duplicates and enforce ref. integrity?

Thanks again for your help!!!!!!!!!!!!!!!!!!
Billiam

"So I think it is best to model it as a many to many relationship
tblInstructors and tblStatus with a junction of
tblInstructorStatusHistory???"
Exactly correct

"Duane, by proper indexes, do you mean I choose Index NO Duplicates in
addition to ref. integrity?"
Mostly correct. I believe if you set up your relationships, the
primary/foreign key indexes will automatically be created.

"Do lookup tables generate the 1 to 1 relationship type, or id that a result
from choosing no duplicates and enforce ref. integrity?"

Lookup table are generally related 1 to many. One record in a Countries
table will be related to many records in an address table.

--
Duane Hookom
Microsoft Access MVP


"Billiam" wrote:

"Duane Hookom" wrote:

It looks like your status table contains only a single record per instructor.
You would need to add at least one date field to note when the status
changed. I would change the name to tblInstructorStatusHistory and create a
separate lookup table for statuses.

To make sure I understand you correctly, I would make the following Tables:

tblStatus
StatusID (Primary key, autonumber, long integer, referential integrity
enforced)
Status (Active, On-Leave, suspended etc)

tblInstructorStatusHistory
InstructorStatusID (Primary key, autonumber, long integer, ref. integrity
enforced)
InstructorID (Foreign key, number, long integer, indexed NO duplicates)
StatusID (Foreign key, number, long integer, indexed NO duplicates)
DateFrom
DateTo

As far as the relationship between these tables, I am not sure what it would
be:
One Instructor has One status at a time, possibly many over time
One status can apply to many instructors

So I think it is best to model it as a many to many relationship
tblInstructors and tblStatus with a junction of tblInstructorStatusHistory???

If you want to keep a history of addresses, your table structures would be
similar and include a date field. If you want to create a combo box for
selecting a country, I wouldn't necessarily use a CountryID field unless you
had a meaningful Country ID.

I am using the ISU country number (ie USA=1, Canada=2 etc) as the CountryID

In the US, we have State names and State
Abbreviations the could be used on a lookup table.

Having the lookup table for regions, cities, and countries would not affect
the performance very much as long as there were proper indexes.

Duane, by proper indexes, do you mean I choose Index NO Duplicates in
addition to ref. integrity?

Do lookup tables generate the 1 to 1 relationship type, or id that a result
from choosing no duplicates and enforce ref. integrity?

Thanks again for your help!!!!!!!!!!!!!!!!!!
Billiam