I am developing a database to automate the creation of courses and classes for the company I work for. Here are the tables that I have (with Primary Keys (PK) and Foreign Keys (FK)). I would love some feedback on whether these tables are normalized correctly (to at least 3N) and whether their relationships are correct.

Classes
ID (PK) Autonumber
ClassName
CourseID (FK) Number
StartDate
EndDate
StartTime
EndTime
ClassMin
ClassMax
RestrictedClass (yes/no)
KnowledgeClass (yes/no)
Materials (yes/no)
MaterialNotes
EmpID (FK) (this is the employee that requested the class to be created)
LocID (FK) (Location)
FacID (FK) (Facility)
RoomID (FK) (Room)
InstructorID (FK)

Courses
ID (PK)
Title
Duration
more...

Employees
EmpID (PK)
FName
LName
Phone
Email

Instructors
InstructorID (PK)
FName
LName

Locations
LocID (PK)
Location

Facilities
FacID (PK)
Facility
LocID (FK)

Rooms
RoomID (PK)
Room
FacID (FK)
LocID (FK)

Here are the relationships that I have:

Course.ID 1:M with Class.CourseID
Employees.ID 1:M with Class.EmpID
Instructors.InstructorID 1:M with Class.InstructorID
Locations.LocID 1:M with Class.LocID
Facilities.FacID 1:M with Class.FacID
Rooms:RoomID 1:M with Class.RoomID

Now here's the issue that I have. Is there a 1:M relationship between Facilty and Locations as well as Rooms and Facility and Rooms and Locations, or is there a M:M relationship?

Are my tables normalized well enough?

Sorry about the long post but I'm really stuck on getting this right. Thanks in advance.

It looks pretty good to me. The true test will be, as always, be the forms and reports, so try a couple. hth.

Thanks for your quick reply.

I do get some errors in the form when I choose to save the record. The error says : The Microsoft Jet database engine cannot find a record in the table Instructors with key matching field(s) InstructorID

Of course, that's when I don't input an instructor in the form. If I do, I get another error for another field. If I set the default to Null, why would this error still show?

Have you checked, in the relationships, is the referential integerty checked? How about cascade update and delete? Check all the relationships. hth.

yes, all three are checked. Thanks for your help. I will try and figure out what is causing this.

Hi
Just a quick point, If all of your PK fields are Autonumber datatype (as they should be) then cascade updates is a 'worthless' option to have checked. Autonumbers cannot be edited and therefore cascading something that cannot be changed is unneccessary.
Rather than having start/end Date, and start/end time fields, have the date and time in a single field, this can be easily parsed out at run time.
Your 3 boolean fields (Yes/No) are they mutually exclusive? i.e. if it is a restricted class, can it also be a knowledge class? If not, then this should be a single field, otherwise you are repeating groups. (even if not you might be better separating this out to another pair of tables (ClassType and ClassesClassType)
Instructors and Employees should be in a single table with an indicator field for employee/instructor.
As to whether there is a relationship between specific tables, only you can really answer that as it is your data. However:
Does each location have a Facility (0, 1 or more?) if this IS the case then yes, you need to create a relationship between the two. Each facility presumably has 0 1 or more rooms, therefore another relationship is required etc etc.
Just My tuppence ha'penny.
Jim

Interesting points, Jim. I have been using a auto-sequence numbering for my PKeys and I have found I need cascade updates and deletes. There are so many different ways of doing thing in Access, it never sees to amaze at how many different way you can do something. Just my two cents for what it is worth.

Hi quest.
Cascade deletes are good in that they ensure referential integrity from the point of view of no orphan records. However if you follow a good practice of not REALLY deleting anything but rather flagging deleted records, even cascading deletes becomes redundant (however I agree that this is simply a matter of being a different way of skinning the cat) I would reccommend that if you do use cascade deletes that you employ fairly stringent rules as to who can delete records and have some sort of paper trail for deleted and cascade deleted records (causing more 'paper' than the simple method of 'flagging' deletes)
However I totally disagree with you on cascade updates. Cascade update will ONLY work on the PK-FK. i.e. if you change the PK value, access will look for all matching FK values and change them to the new value. since you cannot change an Autonumber value, cascade updates will do absolutely nothing, and is therefore pointless to include.
JMHO
Jim

I do use delte stringently, in fact only in the achive process where the record is appended to another dbase and then deleted from the active dbase. Interesting point on update, I will have to play with it and see, I have always had it on and I have not had any troubles with the number proceedures, knock on wood. Thankns for the info, I appreciate it.

No problem at all mate. You're not likely to get any trouble with having cascade updates switched on, it's just not necessary that's all. (IMG:http://www.utteraccess.com/forum/style_emoticons/default/frown.gif)
Jim

another issue involving this database. I am creating a form based on a query using fields from multible tables. When I run the form, I am unable to input data into the field.

Example using above tables: I cannot input an Employee's First Name, Last Name or Phone #. The input fields are bound to their respective table fields in the query. If I unbound them, then I can type in data, but then the data does not return to the tables.

Any suggestions as to why this is happening?

Here is the SQL statement created by Access (if this will help):


SELECT Facilities.Facility, Locations.Location, Rooms.Room, Classes.LogID, Classes.LiaisonID, Employees.FName AS Employees_FName, Employees.LName AS Employees_LName, Employees.Phone AS Employees_Phone, Organizations.Organization, Classes.ClassName, Classes.StartDate, Classes.EndDate, Classes.StartTime, Classes.EndTime, Classes.ClassMin, Classes.ClassMax, Classes.RestrictedClass, Classes.KnowledgeClass, Classes.Materials, Classes.MatNotes, Classes.Registration, Classes.BillingMethod, Classes.CostCenter, Courses.Title, Liaisons.FName AS Liaisons_FName, Liaisons.LName AS Liaisons_LName, Logistical.FName AS Logistical_FName, Logistical.LName AS Logistical_LName, Logistical.StreetAddress, Logistical.City, Logistical.State, Logistical.Zip, Logistical.Phone AS Logistical_Phone, Logistical.Email
FROM Rooms INNER JOIN (Organizations INNER JOIN (Logistical INNER JOIN (Locations INNER JOIN (Liaisons INNER JOIN (Facilities INNER JOIN (Employees INNER JOIN (Courses INNER JOIN Classes ON Courses.ID = Classes.CourseID) ON Employees.ID = Classes.EmpID) ON Facilities.FacID = Classes.FacID) ON Liaisons.LiaisonID = Classes.LiaisonID) ON Locations.LocID = Classes.LocID) ON Logistical.LogID = Classes.LogID) ON Organizations.OrgID = Employees.OrgID) ON Rooms.RoomID = Classes.RoomID;

Hi
Often, when using multi table queries, you cannot enter data in them, this is because the joins are creating restrictions. To overcome this, try using Form / subform structures for data entry.
HTH
Jim