Relational database help.

[hargi]

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

 

[rpstewart]

You will need primary keys on both tables. In theory an integer primary key will perform better but you won't notice it in a small database so I'd go with the ID column you already have. You'll need to create a PK on the courses table so you might as well use an auto incrementing field there.

I assume it's a many to many relationship between students and courses so you'll need an intermediary link table containing at least two columns (the PKs from the other tables) with the appropriate foreign key relationships. The PK of the link table should be both columns so that a student is only enrolled in a course once. You could add enrollment dates etc if you wanted but that's not vital.

 

[hargi]

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?

 

[SimonCHere]

Stick the Student PK on the course table as a FK and away you go.

 

[hargi]

Thanks.

 

[sist_si]

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?

Even if a student was only on 1 course, a course has many students doesn't it? ..so you would still need that link table...otherwise with the student PK as a foreign key in the course table you'd get loads of duplicated data...unless it really is one student per course.

 

[rpstewart]

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?

You'd have the course PK included in the student table with the appropriate FK relationship. Strictly speaking you'd need to include a dummy course record as well as a placeholder for students who have not yet enrolled in a course. The link table approach would also work but you'd need some kind of logic either in the application or the database structure to enforce the one to many relationship.

Which approach is better might be dependent on whether this is an academic exercise or a real life one.

 

[suarve]

Read: http://www.geekgirls.com/databases_from_scratch_3.htm

 

[EirePlane]

Even if a student was only on 1 course, a course has many students doesn't it? ..so you would still need that link table...otherwise with the student PK as a foreign key in the course table you'd get loads of duplicated data...unless it really is one student per course.

If a student is on one course, by putting the CourseID as a foreign key in the Student table, you can still have as many students as you like on a course.

 

[SimonCHere]

Doh, mis read the direction of the one to many.
So do what they said!

Simon

 

[hargi]

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?

 

[GravyMonster]

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?

Your Foreign key should be that of the 'one' side of the relationship.

Players
-----------------
PlayerID
PlayerName

Teams
-----------------
TeamID
PlayerID
TeamName

Also, IMO you shouldn't use direct many-to-many relationships. Mostly because they become very convoluted. It's always better to do one-to-many-to-one, eg.

Accounts
-----------------
AccountID
Username
Password

AccountPageAccess
-----------------
AccountPageAccessID
AccountID
PageID

Pages
-----------------
PageID
PageName
Description

 

[sist_si]

If a student is on one course, by putting the CourseID as a foreign key in the Student table, you can still have as many students as you like on a course.

lol...I misread. thats what you get for rushing through a post.
cheers for pointing me to the error of my ways

 

[suarve]

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?

Honestly google this stuff. It's brimming with basic database tutorials. Although the link i posted before explains a lot of it.