Relational database help.

hargi · 17 Nov 2008 at 14:09

Need to get my head around this,

If I have 2 tables say, one called students and one called courses.
Now in the students table I have colums
1st name
Surname
Email
StudentIdcode(unique containing letters and numbers)

And in the courses on I have
Title
Tutor
Room

To make this a relational database I need to create primary keys?

Could in the students one I use the studentIDcode as the primary key as all instances will be unique, but it contains letters as well as numbers, in a book I have read it recommends that all primary keys are set to numbers.
Or would it be better to create a new coloum and call it studentId and set it to auto increment.
Like in the courses table I would create a new coloum called courseId and set that to auto increment?

To link these 2 tables I would then put the primary key of the students table in as a foreign key I the course table? Would I have to do the reverse to?

TIA

hargi · 17 Nov 2008 at 14:28

Cheers stewart.

Say if it was a one to many relationship as a student can only be on one course. so we dont need the intermediary table.

what would happen then?

hargi · 17 Nov 2008 at 15:08

Thanks.

hargi · 17 Nov 2008 at 16:27

This is for an assignment so im been carefull not to use the scinario that they have given me. So thats maybe why that example is not the best to use.

From my knowlege there are 4 relationships i need to know
mandatory one to one
optional one to many
mandotory one to many
and mandotory many to many

All the tables I have are optional one to many. so to do this its just putting the pk of one as the foreign key of another.
Can I ask how do you decide which way to do this

I mean say we have table a and table b
what would make you use primary key of a as the foregin key in b.
rather than using the primary key of b as the foregin key in a? or does it make no difference?