Leaderboard

I would created a pre-populated table "member_no" which contains a row for each of those values. create table member_no ( memno varchar(3) not null primary key, member_id int ); The "member" table would have a conventional auto_incremented numeric id (not re-used). TABLE member_no TABLE member +----------+------------+ +-----------+------------------+------ | memno | member_id | | member_id | name | etc. +----------+------------+ +-----------+------------------+----- | 01 | 1 | | 1 | Curly | | 02 | 2 | | 2 | Larry | | 03 | 9 | | 8 | Mo | | 04 | 17 | | 9 | Fred | | 05 | 8 | | 15 | Wilma | | 06 | 15 | | 16 | Barny | | 07 | 16 | | 17 | Betty | | 08 | null | | ... | null | | 999 | null | When a new member joins (say, id=18) they assigned to the first memno with a null member_id (08). When a member leaves, their member_id in the member_no table is set back to null. You could use triggers on insert and delete to automate the process.

One of those two will work and the other will not. What will happen is, if you do the correct method then it will work, and if you do the incorrect method then it will not work.