Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 05-01-2009
Member
 
Join Date: Dec 2008
Posts: 5
Database relationships problem

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 ?
Reply With Quote
  #2  
Old 05-01-2009
Member
 
Join Date: Oct 2005
Posts: 2,389
Re: Database relationships problem

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.
Reply With Quote
  #3  
Old 05-01-2009
Member
 
Join Date: Dec 2008
Posts: 5
Re: Database relationships problem

Thanks for your reply,
I would like to have one student multiple course but one course can be assigned to only one student.
Reply With Quote
  #4  
Old 05-01-2009
Member
 
Join Date: Feb 2008
Posts: 1,848
Re: Database relationships problem

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.
Reply With Quote
  #5  
Old 05-01-2009
Member
 
Join Date: Dec 2008
Posts: 5
Re: Database relationships problem

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

Thanks for it.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Database relationships problem"
Thread Thread Starter Forum Replies Last Post
Relationships in The Elder scrolls Hardiyy Video Games 5 06-06-2011 11:02 PM
How to make relationships with various entities in MS Access Ferdinand Alosa Windows Software 3 06-02-2011 04:14 PM
Relationships in java Jagadamba Software Development 3 18-11-2009 11:02 AM
How do i assign relationships primary key access Kamran Windows Software 3 19-05-2009 12:42 PM
How many relationships can create in EJB? Pandya Software Development 2 30-03-2009 11:39 AM


All times are GMT +5.5. The time now is 03:07 AM.