Best way to create a unique number for each many to many relationship

Question

I have a table of Students and a table of Courses that are connected through an intermediate table to create a many-to-many relationship (ie. a student can enroll in multiple courses and a course can have multiple students). The problem is that the client wants a unique student ID per course. For example:

rowid Course Student      ID (calculated)
1     A      Ben          1
2     A      Alex         2
3     A      Luis         3
4     B      Alex         1
5     B      Gail         2
6     B      Steve        3

The ID's should be numbered from 1 and a student can have a different ID for different course (Alex for example has ID=2 for course A, but ID=1 for Course B). Once an ID is assigned it is fixed and cannot change. I implemented a solution by ordering on the rowid of the through table "SELECT Student from table WHERE Course=A ORDER BY rowid" and then returning a number based on the order of the results.

The problem with this solution, is that if a student leaves a course (is deleted from the table), the numbers of the other students will change. Can someone recommend a better way? If it matters, I'm using PostgreSQL and Django. Here's what I've thought of:

• Creating a column for the ID instead of calculating it. When a new relationship is created assigning an ID based on the max(id)+1 of the students in the course
• Adding a column "disabled" and setting it True when a student leaves the course. This would involve changing all my code to make sure that only active students are used

I think the first solution is better, but is there a more "database centric way" where the database can calculate this for me automatically?

Answer 1

Your first suggestions seems good: have a last_id field in the course table that you increase by 1 any time you enroll a student in that course.

Answer 2

If you want to have stable ID's, you certanly need to store them in the table.

You'll need to assign a new sequential ID for every student that joins a course and just delete it if the student leaves, without touching others.

If you have concurrent access to your tables, don't use MAX(id), as two queries can select same MAX(id) before inserting it into the table.

Instead, create a separate table to be used as a sequence, lock each course's row with SELECT FOR UPDATE, then insert the new student's ID and update the row with a new ID in a single transaction, like this:

Courses:

Name     NextID
-------  ---------
Math     101
Physics  201

Attendants:

Student Course   Id
------- ------   ----
Smith   Math     99
Jones   Math     100
Smith   Physics  200

BEGIN TRANSACTION;

SELECT NextID
INTO   @NewID
FROM   Courses
WHERE  Name = 'Math'
FOR UPDATE;

INSERT
INTO Attendants (Student, Course, Id)
VALUES ('Doe', 'Math', @NewID);

UPDATE
  Courses
SET NextID = @NewID + 1
WHERE Course = 'Math';

COMMIT;

Answer 3

Creating a column for the ID instead of calculating it. When a new relationship is created assigning an ID based on the max(id)+1 of the students in the course

That how I'd do it. There is no point of calculating it. And the id's shouldn't change just because someone dropped out.

Adding a column "disabled" and setting it True when a student leaves the course.

Yes, that would be a good idea. Another one is creating another table of same structure, where you'll store dropped students. Then of course you'll have to select max(id) from union of these two tables.

Answer 4

I think there are two concepts that you need to help you out here.

1. Sequences where the database gets the next value for an ID for you automatically
2. Composite keys where more than one column can be combined to make the primary key of a table.

From a quick google it looks like Django can handle sequences but not composite keys, so you will need to emulate that somehow. However you could equally have two foreign keys and a sequence for the course/student relationship

As for how to handle deletions, it depends on what you need from your app, you may find that a status field would help you as you may want to differentiate between students who left and those that were kicked out, or get statistics on how many students leave different courses.