MySQL and INT auto_increment fields
- by PHPguy
Hello folks,
I'm developing in LAMP (Linux+Apache+MySQL+PHP) since I remember myself. But one question was bugging me for years now. I hope you can help me to find an answer and point me into the right direction. Here is my challenge:
Say, we are creating a community website, where we allow our users to register. The MySQL table where we store all users would look then like this:
CREATE TABLE `users` (
`uid` int(2) unsigned NOT NULL auto_increment COMMENT 'User ID',
`name` varchar(20) NOT NULL,
`password` varchar(32) NOT NULL COMMENT 'Password is saved as a 32-bytes hash, never in plain text',
`email` varchar(64) NOT NULL,
`created` int(11) unsigned NOT NULL default '0' COMMENT 'Timestamp of registration',
`updated` int(11) unsigned NOT NULL default '0' COMMENT 'Timestamp of profile update, e.g. change of email',
PRIMARY KEY (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
So, from this snippet you can see that we have a unique and automatically incrementing for every new user 'uid' field. As on every good and loyal community website we need to provide users with possibility to completely delete their profile if they want to cancel their participation in our community.
Here comes my problem. Let's say we have 3 registered users: Alice (uid = 1), Bob (uid = 2) and Chris (uid = 3). Now Bob want to delete his profile and stop using our community. If we delete Bob's profile from the 'users' table then his missing 'uid' will create a gap which will be never filled again. In my opinion it's a huge waste of uid's. I see 3 possible solutions here:
1) Increase the capacity of the 'uid' field in our table from SMALLINT (int(2)) to, for example, BIGINT (int(8)) and ignore the fact that some of the uid's will be wasted.
2) introduce the new field 'is_deleted', which will be used to mark deleted profiles (but keep them in the table, instead of deleting them) to re-utilize their uid's for newly registered users. The table will look then like this:
CREATE TABLE `users` (
`uid` int(2) unsigned NOT NULL auto_increment COMMENT 'User ID',
`name` varchar(20) NOT NULL,
`password` varchar(32) NOT NULL COMMENT 'Password is saved as a 32-bytes hash, never in plain text',
`email` varchar(64) NOT NULL,
`is_deleted` int(1) unsigned NOT NULL default '0' COMMENT 'If equal to "1" then the profile has been deleted and will be re-used for new registrations',
`created` int(11) unsigned NOT NULL default '0' COMMENT 'Timestamp of registration',
`updated` int(11) unsigned NOT NULL default '0' COMMENT 'Timestamp of profile update, e.g. change of email',
PRIMARY KEY (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
3) Write a script to shift all following user records once a previous record has been deleted. E.g. in our case when Bob (uid = 2) decides to remove his profile, we would replace his record with the record of Chris (uid = 3), so that uid of Chris becomes qual to 2 and mark (is_deleted = '1') the old record of Chris as vacant for the new users. In this case we keep the chronological order of uid's according to the registration time, so that the older users have lower uid's.
Please, advice me now which way is the right way to handle the gaps in the auto_increment fields. This is just one example with users, but such cases occur very often in my programming experience.
Thanks in advance!
