I have a problem trying to get my head around a database problem. The situation is as follows for a school enroling system.
Table 1 : School CREATE TABLE school (schoolID auto_increment, school_name varchar, etc etc)
Table 2 : Students CREATE TABLE students (studentID auto_increment, student_name varchar, etc etc)
Table 3 : Enrolments (problem is here I believe) CREATE TABLE enrolments (enrolID, ...)
The issue is, previously the scope for the system was to allow enrolments to come from any number of locations, at any time, and would just insert into the database, which was fine, everything was autoincremented.
The scope changed, as they did not like the fact, that when reports would be done, when they retrieved the enrolments the numbers for the enrolments were scattered. They wanted each schools enrolment ID's to be sequential.
One thought I had was, option (a)
Create an enrolment table for every school (looking at around 1000-1500 total schools), this way every enrolment inserted, would be stored sequentially.
Option (B)
Have another table that would record the "lastrecord" inserted for that school.
CREATE TABLE lastrecord (schoolname varchar, nextrecord int)
With this, before every insert, it would have to check the database to find what the last ID used for that school was, retrieve the value, +1 to it, and then INSERT into the enrolment table.
using this method I would prefix the enrolment ID with the schools ID, for example schoolID : 100
new enrolment ID : 10045 for their 45th student.
Then after every insert, update that table with the record used. This seems extremely inefficient but I am struggling to work out a better solution.
thank you in advance for any help.
You could make the autoincrement on two fields
Like:
ADD PRIMARY KEY (school_id,enrolID)
and set enrolID to autoincrement
Then each school will have its own autoincrement