Home » SQL & PL/SQL » SQL & PL/SQL » Argh, some help please
icon2.gif  Argh, some help please [message #210604] Thu, 21 December 2006 08:51 Go to next message
avion
Messages: 11
Registered: December 2006
Junior Member
Hi,

I would like to know if there is anyway I can prevent having to enter duplicate data for the following 2 tables I've made.

I've got 2 tables: 'Trip'(trip_Id) and 'Student' (student_Id)

1 student can visit many trips, and I've linked all student_Id's to the Trip table as a FK column.

The Trip table has columns detailing the trip details such ticket prices and time of departure etc. If more than 1 student goes to the same trip, I have to enter the same trip details x amount of times for those students. The trip_Id (PK) isn't unique either, as i have to list it over and over again for the students that go to the same trip..

Is there any way I can avoid this? I've been sitting here for 2 hours and I can't work out a solution!

create table Trip (
trip_Id number,
Ticket_Prices number,
Departure_Time date,
student_Id number,
foreign key student_Id references Student(student_Id),
primary key (trip_Id)
);


create table Student (
student_Id number,
Forename varchar(50),
Surname  varchar(50),
primary key (student_Id)
);


PS. I am a beginner, please go easy on me Smile

Thanks Smile


[Updated on: Thu, 21 December 2006 08:52]

Report message to a moderator

Re: Argh, some help please [message #210609 is a reply to message #210604] Thu, 21 December 2006 09:08 Go to previous messageGo to next message
ksoule
Messages: 13
Registered: November 2003
Location: Austin, TX
Junior Member
As you said a student can visit many trips and a trip can be made by many students (more than 1 student goes to the same trip). right?
you will have to create another table (ie Students_trip) with the following structure:

create table Student_Trips (
trip_Id number foreign key refrences trips(trip_id),
student_Id number,
foreign key student_Id references Student(student_Id),
primary key (trip_Id,student_id)
...
);

When a student travel , you'll enter the student_id and the trip id , details will be pull up from student/trip by a query, no need to enter the same detail every time.




Re: Argh, some help please [message #210616 is a reply to message #210604] Thu, 21 December 2006 09:35 Go to previous messageGo to next message
avion
Messages: 11
Registered: December 2006
Junior Member
Thanks for your reply.

Indeed it looks like a M to M relationship. And the table you have made looks like the perfect solution. One thing though, The student_Id (FK) that is in the Trip table, no longer needs to be there now, right?

Thanks again Smile
Re: Argh, some help please [message #210625 is a reply to message #210616] Thu, 21 December 2006 10:08 Go to previous message
ksoule
Messages: 13
Registered: November 2003
Location: Austin, TX
Junior Member
Correct you don't need the student_id(FK) in the table trip.
sorry I forgot to mention that.
Good.
Previous Topic: Need cyrillic output with commandline tool sqlplus on linux
Next Topic: max(decode) v/c sum(decode)
Goto Forum:
  


Current Time: Tue Dec 06 11:57:31 CST 2016

Total time taken to generate the page: 0.09931 seconds