table design analysis needed (1 Viewer)

[metrodub]

I am designing a database for my company that keeps track of new classes and new courses created. I have a form for each with the create a class form taking the course title from the courses table/created course from the create a course form.

Here's my dilemma. I want to use cascading combo boxes for class location, facility and room but I think my table structures and relationships may be incorrect.

Here is a list of my tables and their fields:

tblClass
ClassID (PK)
CourseID (FK)
InstructorID (FK)
StartDate
EndDate
StartTime
EndTime
ClassMin
ClassMax
Materials (yes/no)
MatNotes
EmpID (this is the ID of the employee who requested a class creation)
LocID
FacID
RoomID
LogID (Logistical Coordinator ID, if applicable)

Course
CourseID (PK)
CourseName
more...

Employee
EmpID (PK)
FName
LName
more...

Instructor
InstructorID (PK)
FName
LName
more...

Logistical
LogID (PK)
FName
LName
more...

Locations
LocID (PK)
Location

Facilities
FacID (PK)
Facility
LocID (FK)

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


When I run a query with class, locations, facilities, and rooms no information shows up. If I run the query with the relationships between facility/room, facility/location and room/location removed (leaving the only relationships between class and these three) it works. Why is that? Shouldn't there be a 1-M relationship between Location, Facility and Room? Or, should there be a M-M relationship and I should create tables with each PK?

I apologize for the long post but this has become quite frustrating (despite the simple logistics).

Thanks!

 

[aleb]

I think there is a redundancy in keys
Location>Facility>Room>tblclass

all of them 1-M relationship

while in your structure
tblroom has redundant key - Facid
tblclass has 2 redundant keys - Facid, Locid