I am looking for alternate methods to ID'ing rows in my tables. I have many diferent tables with diferent attributes and rates of inserts, updates, and deletions. I am using MySql/PHP.
Currently I am using:
INT AUTO INCREMENT for tables where very few inserts will occur and once the data is in there it will never be deleted.
DATETIME for tables that are updated continuously and have many inserts and many deletions.
Both of these types of tables will be used by only one or two users.
For some new tables that will potentially be used by 100's of users at the same time I am looking for a new method to ID the rows. AUTO INCREMENT doesnt work well as I do not want to have to continuously clean up my tables and the ones connected to them as the numbers get out of wack with deletions. DATETIMES pose the problem of multiple users inserting rows within the same second of eachother and not having a unique ID.
I was thinking maybe using a VARCHAR and building ID's with microtime and date.
I have read several posts in reply to people wanting to reorder their tables and use up the deleted number. I do not wish to have to export and re-insert the data, THEN, update 20 other tables that have the old ID in it.
I am just looking for other suggestions. I am well aware how AUTO INCREMENT behaves and that its nicelly inserting a unique ID for each insert.
If an ID can be based on date and time, that will never have to be worried about down the road.
Has anyone used any other methods then INT AUTO INCREMENT, or DATETIME?
I've just read the MySQL manual part about AUTO_INCREMENT, and according to user comments, the behaviour of such fields can change from one version of MySQL to another. Consequently, I would really avoid the use of these.
Moreover, I don't know how such a field behaves when reaching its maximum value (depending on its datatype). Maybe r937 can give advice on that point ?
Another common way, and the one used when you build a nice database design, is finding the list of columns that uniquely identifies each row in your table, and make this combination of columns the primary key. In your case, maybe adding one or two columns to the DATETIME one could do it. I think that you could also look for documentation on "Database design" : that may help you understand more about good database designing, and among other things, about how to choose primary keys.