Search code examples
sqldatabase-designquery-optimizationwhere-clauseunique-key

Composite primary key? Or an auto increment primary key with unique composite index?

I have two tables named Student and Course and a join table named StudentCourse. Both Student and Course have an auto increment integer Id.

StudentCourse table will have at most 10,000 records (not more!) and I will use this table for select some data in most scenarios. Inserting performance is not my concern now and I just want to get the data fast!

My queries are in form of:

select * from studentcourse where studentid == sid and courseid == cid
select * from studentcourse where studentid == sid

Which one of implementations below is better? Why? Is it always better than the other or it depends? Is there even any better solution for my senario?

1: Using an auto increment Id column for StudentCourse as primary key and set a unique composite index like (StudentId, CourseId).

2: Using a composite primary key like (StudentId, CourseId)

3: Creating table with no primary key and just use the unique composite index like (StudentId, CourseId).

Update

What if we have some other properties that only matters when we have a relation! Consider Score or Grade as a 3rd column in StudentCourse table. Should we use 1st option in this situations? I mean lets assume we are using an ORM and we should have a reference to StudentCourse class (entity) in our Student class (entity).

Solution

  • 2 is the best.

    It's better than 1 because it has one less index (so less space/cache is taken and the index maintenance is less expensive). A surrogate key (like Id) is generally useless in a junction table.

    It may be better than 3 depending on your DBMS: some DBMSes will not allow you to cluster the table on a unique index (just on a primary key)1. Even on a DBMS that allows it (like SQL Server), I'd go with 1 for being more direct and having simpler syntax.

    1 And some will not allow you to reference a unique index (just PRIMARY KEY or UNIQUE constraint) from a FOREIGN KEY, though that's probably not relevant here. If it was, than you'd consider the surrogate key Id.