When developing a structure for your database, there comes the question of how to pick a unique ID for your tables. In my experience, you have about 3 good options, which I would outline below.
1. Auto Increment
Auto increment is usually a good option, especially when you have a table from which data may be deleted from time to time. It’s more reliable than the next option on this list. In the last year or so, I have used auto increment because I had a problem with deleted data when I used option #3 below. Another problem with this is that the size of the ID’s will increase, depending on the growth rate of the table data.
My advice: if you’re gonna delete data from your table from time to time, try using auto increment. Also, if you have a table who’s data doesn’t increase drastically over a short time, this might also be a good option.
2. Manual (Generated) ID
Before I started using auto increment, I usually generated my unique table ID’s using a simple PHP function that guarantees no repeats or conflicts even when data was deleted. It also guarantees a fix length for the ID field, which in turn limits the range of numbers that can be used for IDs (eg, 4 digits, or 6 digits). Nevertheless, this method of mine can generate up to 8 digits uniquely, if needed. Until now, about 6 digits have met my night as my tables are not huge.
My advice: if you want a fixed length for you table’s unique ID field, a function to generate ID’s would be a good choice. Usually, I just select the last 6 digits of the number of seconds passed since the Unix Epoch or simply…
3. Manual (Counted) ID
When I started in PHP, one of the methods I saw people use was to count the number of rows in the table, then increment by 1 to generate the next unique ID. For a while now, I haven’t seen this method used anywhere recently. This method also has the same problem as in method #1 above. If you have 2 items in the table, the next generated ID would be 3. So now you have 3 times. If you delete 1 item, the next could would be 3 and that would conflict with the already existing ‘3’ in the table. This method requires more coding to count, and if you want to check that the newly generated ID doesn’t exist in the table, you’d require more coding again lol
My advice: if you aren’t going to delete from the table, you may stick with it. However, I do not recommend this method. Stick with method #1 or #2 above, accordingly.
If you have other ways you assign unique ID’s to your database table, you may share in the comment section below (if you don’t mind)