Databse design for a teacher application

[James89]

Hi all, hoping you can help me out with my database (you can see it attached). What I need to design is a database for a taecher to use to help them with a class. So as this will only be used by one teacher there is no need for a teachers table. However, the teacher may teach more than one class and a student may be in more than one class taught by that teacher. For this I have created the 'Enrollment' table. This means that a student can be in multiple classes with the unique Enrollment ID. However the problem I have is that it is letting a student enroll in the same subject more than once as only the key needs to be different.



Eg.

1 Jenny Maths
2 Jenny Maths

This should not be able to happen, however if I drop the Enrollment key and link classes straight to students, it means that each student can not be in more than one class.

Any other feedback about the database/schema design very welcome.

[AccessMSSQL]

You need to create a combination key on Enrollment table. Make Enrollment Number, Student Number AND Class Name the primary key.

[ssanfu]

Since you are in development, do not use spaces in object names. Only use letters, numbers and the underscore (_). Also, be aware of reserved words - see http://allenbrowne.com/AppIssueBadWord.html

[James89]

Thank you very much. I made those three primary keys, but now am having trouble making a relationship between the Enrollment Numbers. In the relationship view any time I join them and try to enfore referential integrity, I get the following message: "No unique index found for the referenced field of the primary table"

(Updated schema attached)

Thanks

EDIT: ssanfu, I just saw your post. I'll be sure to have a look and make those changes. Thank you.

[ssanfu]

I would recommend that all primary keys be a long integer type. I always use autonumbers.... there is some controversy because they are not natural keys, but it works for me. I don't show then on forms because they have no use except to link records in different tables. My 2 cents.......

[James89]

My primary keys are as follows:
Enrollment_Number: AutoNumber
Student_Number:text (Because the numbers have a letter before them...this is a must, I can not change it)
Class_Name:text (the class names are unique, i could add a number, but didn't see a point as the names are already unique)

EDIT: I tried creating a new db with just Enrollment and Attendance and the same problem occured. It would and will only work when I have 1 PK (Enrollment_Number) in the Enrollment table. Anything else gives me the same error "No unique index found for the referenced field of the primary table"

[ssanfu]

RE: Text PK's

See https://www.accessforums.net/showthr...er-application
Chapter 3, then scroll down to "Data Types for Key Fields"