Database Design help

[AccessNewbie010]

Hello all,

I'm having a problem connecting two tables ( room table and approved instructor table) to the class table. Whenever i try to connect the room table to the class table and approved instructor to the class table ( both should have a one-to-many relationship to enforce referential integrity), I'm not able to because whenever I the result is always an indeterminate relationship.



I took a couple of snapshots of my database; each assistance is appreciated

[Rod]

OK, your perceived problem is being caused by the fact that you are joining a component of a compound key (Instructor_Id and Room_no) to the foreign key in the table on the many side of the relationship. To attempt to do so and end up with a 1:M referential relationship is nonsense - viz. how many rows will there be in the Approved Instructor table with identical Instructor_Id? You must relate both Instructor_Id and Course_No to corresponding foreign keys in the Class table. That is why the relationship definition pane has a grid, i.e. for additional attributes.

This is a good example of why many of us hate compound keys!

BTW there's a mistake: you've related Course_NO in table Course to Instructor_Id in table Approved Instructor.

While on the subject I suggest you should adopt a naming convention (Reddick?) and avoid spaces or special characters in your names. Yes, I know Access allows it but you'll be sorry.

[orange]

Further to Rod's comments, I recommend you write down WHAT your business (the rules) is/are all about. You have shown a picture, but we don't really know what the picture is suppose to represent. Ideally your data base will be designed to support your business, so having a clear statement of business facts will be needed to vet your database design.

Here's a small sample from databaseanswers.org. This sample is meant to show the sort of things involved --these may not be exactly related to your situation.

[AccessNewbie010]

I apologize for the late response, but thank you all for responding. I am taking a database concepts class, and I was only given an ERD diagram including column constraints to create the database; maybe it is a poor choice of an example, but it was assigned to me. I will make the corrections, and again thanks.