It's easy to create a database table for storing sequence numbers ; but this design is suited for the event when the sequence is shared for all users. What I want is to create sequence for each group of users : this group can grow at any time because it's a database table , that is the administrator can create a group at any time and users are assigned to a specific group. So how to implement the sequence generation according to a group ?
if you are using myisam
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
Below extracted from above links.
For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.
CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (grp,id)
) ENGINE=MyISAM;
INSERT INTO animals (grp,name) VALUES
('mammal','dog'),('mammal','cat'),
('bird','penguin'),('fish','lax'),('mammal','whale'),
('bird','ostrich');
SELECT * FROM animals ORDER BY grp,id;
Which returns:
+--------+----+---------+
| grp | id | name |
+--------+----+---------+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+--------+----+---------+
For your case:
CREATE TABLE mytable (
user_id MEDIUMINT NOT NULL AUTO_INCREMENT,
group_id MEDIUMINT NOT NULL,
user_name CHAR(30) NOT NULL,
PRIMARY KEY (group_id,user_id)
) ENGINE=MyISAM;
INSERT INTO mytable (group_id, user_name) VALUES
(1,'alex'),(1,'jenny'),(2,'baz'),(1,'tim'),(2,'danny'),(3,'joe');
SELECT * FROM mytable ORDER BY group_id,user_id;
Returns:
user_id group_id user_name
1 1 alex
2 1 jenny
3 1 tim
1 2 baz
2 2 danny
1 3 joe