Database Design Normalization help

External Since: Aug 27, 2007 Posts: 61

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!

External Since: Feb 07, 2005 Posts: 3294

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!