Database relationships problem

[Barny]

Hi Friends,

I am newbie to Database technologies. Can anyone please help me with this. Which of the following is the best way in defining relationship between two entities?

SCHEME 1
Table - Course
Course_id
Course_Name
Course_details

Table- Student
Student_id
Student_Name
Student_Roll No.
Course_id

SCHEMA 2

Table - Course
Course_id
Course_Name
Course_details

Table- Student
Student_id
Student_Name
Student_Roll No.

Table Student_Course
Student_id
Course_id

As per my understanding in SCHEMA 1, by having course_id column in student table we imply that every student must have a course, i guess i am right? Whereas in SCHEMA 2, we mean that course and student exist independent of each other and there may be a relationship between them.

How do we look at these schemas when we develop an application over it, from same point of view as i did? I mean we can force to select or enter a course for a student record in SCHEMA 2 using application logic or we can skip (by passing null value) course details of a student in SCHEMA 1.

For example somebody is entering bulk records of students in the system and he may or may not aware of student's course and may want to skip for the moment? Which schema should be chosen in this case?

Above scenario was built for two tables, how about having multiple relationships e.g student_exam, student_sport, student_activity etc ?

[Reegan]

Hi Barny,
There are two different kind of relationships between course table and relationships table.There is a relationship of 1:n in your first schema. This means, there could be an unknown number of students for every course, but for every student there could only be one course.

Whereas contrasting the first schema, your second scheme defines another relationship referred by n:m relationship. This means, that there can be an unknown number of students for every course, and for every student there could be an unknown number of courses (between 0 an n).

Since these are two different relationships it is upto you which relationship you want to use. Be sure and logical of it, which of your schemes you should use.

[Barny]

Thanks for your reply,
I would like to have one student multiple course but one course can be assigned to only one student.

[MindSpace]

As per your requirements your schema 1 describes a 1:n relationship between student and course, so you need to change that. Since you want to implement a 1:n relationship between course and student.

It means n courses if you look from the student side, and one student if you look from the course side. The identifying feature would be the student id in a course table.

But the case will be different for private courses, you will probably have a course with multiple time slots for multiple students.

For example, at 07:00 there is an Mathematics course for student Amy while there is another one at 9:30 for student Dean.

In above case the schema can be:

Table - Course
Course_id
Course_Name
Course_details

Table- Student
Student_id
Student_Name
Student_Roll No.

Table Student_Course
Student_id
Course_id
start_time
duration

As long as you have a unique constraint over all four columns, this could meet your requirements.
It's just my experience, maybe this idea is absolutely unnecessary.

[Barny]

Thanks Mindspace,
You made things clear for me, using unque constaints i can limit my database relationships.

Thanks for it.