The site I am designing for is in the final stages of development. It is a forum where, obviously, users ask and answer questions.
The owners want id values to be of no value to competitors. These would be the id fields such as question_id, user_id, answer_id, and a couple of others. In other words, they dont want the value of ids in the site to be able to be used by a competitor to determine how many users, or questions, or answers exist. This would also include a private message section since you could sign up, message me, see message_id=3189 in the url or in the html in case of post, and start to piece together how active the site is.
Ok. So, the expectation is for ids to get into the millions (whether it will or not, we are planning for 5 million for each id)
Currently, we are using auto-increment for each table (type is INT unsigned)(ex.questions table has question_id as auto, answers table has answer_id as autoincrement, etc.) and of course there are many joins, and all works well. Even when I insert millions of test records. But, to be honest, I am skeptical of this testing--just adding records--since I am unsure of if there is an impact with multiple users.
We are considering three options to convert the ids so that we make them not usefull to the outside world. And I would love some thoughts from you guys.
1) Add a 4char hash column next to each id column, and index it. Show this hash in the urls. Then whenever a something needs to be processed, lookup the hashes, gete the autoid, and use that in joins and inserts. The con here is the added code and added db calls--sometimes will need two or three hashes to be resolved. Not sure of performance with the lookups.
2) Add the 4char hash column IN PLACE OF the autoid column. And in joining tables make, obviously, the same change. So, the 4char is the primary key. The pro here is that there is very little change to code, just change my db column types. But not sure of performance with the joins or lookups.
3) Generate random numbers for the id field instead of autoincrementing. This gives billions of random possibiilities which would be of no value to others. I would probably opt for only using numbers above 1 billion so all ids are the same length. The pro here is that it is the easiest, but not sure of lookups and joins with fragmented ids.
Well, what do you guys think? Do any of these make more sense than the others? Is this a case of "I'd never notice a difference anyway, even with millions of records?"