Option 1

courses_availible(course, semester, year) primary key (all of these)
course_timetables(course, semester, year, timetable_info, day, etc)

Option 2

courses_availible(id, course, semester, year) primary key (id)
UNIQUE (course, semester, year)
course_timetables(id, timetable_info, day, etc)

Which is your option of choice?

Hi,

I go with OPtion-2

ahh, now what about your future join statements

Find timetable for course x in sem y and year z

Option 2
Select timetable_info
from course_timetables c1, courses_availible c2
where c1.id = c2.id AND
c2.course='x' AND
c2.semester='y' AND
c2.year='z'.

you now have to match the child ID in course_timetables with its parent in courses_availible.

The trade off. Although this was just to reply with some mild reasoning.

Yes, there is always a trade off. I was reluctant to vote one way or the other, because there are times when I might do one, and other times where I might do the other.

Generally I prefer to avoid overusing surrogates. I use them where:
a) no natural key exists (like with people)
b) the natural key is likely to be updated in the future
c) the natural key is long and cumbersome (e.g. long text string)
d) the natural key consists of many columns, and is referenced by foreign keys

In this case I think only (d) really applies. The natural key exists, it is fairly unlikely to be updated, and it is not cumbersome - assuming "course" is a code rather than free text. This leaves me unsure as to whether the benefit of being able to join via a single column outweighs the benefit you mention of often not needing to join at all, because the child table contains sufficient information to be menaingful.

I used to be decisive, but these days I'm not so sure...

"d) the natural key consists of many columns, and is referenced by foreign keys"

Many columns is obvious although try defining too many. However is your reasoning behind the referencing by foreign keys related to updates of all further tuples where the primary key is referenced, just wanted to clarify that.

No, just the fact that joins will involve several conditions, which can be tiresome to type. However, I don't consider that a major issue - and one can always create handy views to pre-join data. With 3 columns, it's not a big deal - if it were e.g. 7, I'd be more inclined towards the surrogate.

Option 1 -- I prefer and have benefitted over the years using natural keys. The only exception I've encountered so far is employeeIdentificationNumber; I defy you to find a natural key related to a person.