Database Design Normalization help

I provide help with Access, Excel and Word applications for a very
reasonable fee. I have reviewed table structure designs on many occasions
for customers. I provide comments and recommendations in a report and a map
of the tables. If you are interested, contact me at rlaird.TakeThisOut@penn.com.

Steve




"Billiam" <Billiam.TakeThisOut@discussions.microsoft.com> wrote in message
news:29CC08CD-AE3D-49AD-82E8-A0B6895F3FAE@microsoft.com...
>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!

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" <Billiam.TakeThisOut@discussions.microsoft.com> wrote in message
news:327D0D26-6FC0-4837-B0FB-B7B6C190CE45@microsoft.com...
> 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" <nonsense RemoveThis @nomsense.com> wrote in message
news:E_WdneX4SqPRWhXVnZ2dnUVZ_tfinZ2d@earthlink.com...
> 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

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" <nonsense DeleteThis @nomsense.com> wrote in message
news:E_WdneX4SqPRWhXVnZ2dnUVZ_tfinZ2d@earthlink.com...
> 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" <Billiam DeleteThis @discussions.microsoft.com> wrote in message
> news:327D0D26-6FC0-4837-B0FB-B7B6C190CE45@microsoft.com...
>> 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!
>
>