Using composite keys but stuck badly in the design

[hmushtaq]

I need some help regarding the relationships, as I am badly stuck with them. As you can see in the attached picture, I am using composite keys to create the Relationship table, "REGISTERED_COURSES". From this table, I would like to create a relationship to another table, "ACADEMIC_RECS". I have added all three fields from the primary table to keep the one-to-one relationship between the two(which is actually what is required). But on the other hand, things have become ugly. As with the table, "ATTENDANCE", I only need the two fields, "CourseID" and "TraineeID", but not the "RegDate", as it is not related to the attendance table. If I do not add all three fields, the relationship between "REGISTERED_COURSES" and "ATTENDANCE" will become "Indeterminate". How can I resolve this issue(without adding surrogate keys). You can ask me for more information regarding my DB requirements if you need.



Thanks a million!

Hunain

[ssanfu]

How can I resolve this issue(without adding surrogate keys).

Why can't you use a surrogate key? Business rules?

After looking at your relationships, this is what I came up with. It does use a surrogate key, but only for the attendance table.

"Reg_CourseID" is an autonumber, indexed - no duplicates.
"Reg_CourseID_FK" is a long

I have added all three fields from the primary table to keep the one-to-one relationship between the two(which is actually what is required).

I would combine the two tables that are related 1:1.
Is this a business requirement?

[hmushtaq]

Thank you for your reply ssanfu. I highly appreciate that you took out your time to consider my problem.

I am not very experienced with databases, so I am still new to some concepts and trying to implement them the best way I understand them.

Regarding your solution, as I see in the REGISTERED_COURSES table, you have added another field, "Reg_CourseID", which is not a primary key. But I am assuming that it is going to be unique, as then you are relating this field to the attendance table with a foreign key, "Reg_CourseID_FK".

Because I already have 3 fields making up the composite key, adding another key as a unique key, isn't it a bit too much for indexing? Do you suggest I should get rid of the composite keys in this table and only use the Identity key as primary key?

My requirement is that a trainee should not be able to register for the same course again on the same date. A trainee can register more than one courses, but there is a time limit to it. That I will manage through coding.

For ACADEMIC_RECS table, as you suggested that it is better to combine them to get rid of the one to one relationship, but my requirement is that I will be adding more fields to it such as, there will be 5 to 7 quizzes(which I will be storing as different fields for each quiz), then final marks and grade. Do you think it is better to keep all these fields in the REGISTERED_COURSES table, where to me it does'nt seem fit that along registered courses, I would also be storing the academic records.

I need your opinion, as I may be wrong with my assumptions.

Thanks alot

Hunain