:
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
