MySQL Design - What is best primary key for our needs?

Question

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?"

Answer 1

"Random" keys (UUIDs, MD5s, etc) can cause a performance problem, so let's avoid them as indexes (including PKs) of tables.

What is exposed? Stuff in the url parameters? Nothing else?

Well, the exposed "numbers" could be encrypted copies of the AUTO_INCREMENT values that work nicely otherwise. Pass the encrypted stuff wherever a competitor might see it, then decrypt it when you need the simple id.

That is, I want to solve the problem at a higher level than in the bowels of the schema.

One approach is

AES_ENCRYPT(CONCAT(id, '-my-salt'), 'my-secret-string')

together with a similar AES_DECRYPT. See MySQL, PHP, and other documentation. The "salting" is to avoid a certain level of easy decoding.

You should probably do the en/decryption in the client and assume the connection between the client and MySQL is secure. (After all, 'my-secret-string' is quite visible to a hacker on the client or server or network between them.)

AES strings are, I think, 8-bit values. It would probably be good to use BASE64-encoding to make them nearly url-friendly. You probably still need to "urlencode" to feed to urls.

Note also,...

• MD5 is not reversable.
• Random numbers are too likely to duplicate.
• Truncate AES strings can't be reversed; I tried to minimize the length of the full AES string, but they will still be somewhat fatter than the minimum.

Answer 2

How about this for a solution.

Start the autoid at 2 billion (to digits)

Retrieve the id. Also retrieve two random indicators that will have chars from 0-9, a-z, A-Z (3,844 different possibilities).

Based on each possibility of indicator1, add/subtract from the autoid.

For example, lets say the autoid is 1,000,000,000 and the indicator1 is "b"

Function AddSubtractID(id, indicator1) 'each string will have a different value to add/subtract Select Case indicator1 Case "0": newId=id + 501,210,031 ... Case "9" newId=id + 822,882,432 Case "a" newId=id - 435,230,771 Case "b" newId=id + 761,991,321 ... Case "Z" newId=id - 543,431,332 End Select End Function

Then, take the newID and map each digit to a random char with indicator2.
Function MapChars (id,string)

strMap="wuIS813Sf3" 'random
Select Case string
  Case "0": strMap="qo9U8dj2L2"
  ...
  Case "9": strMap="v9wl2Yh8S1"
  Case "a": strMap="lPs07h2LsH"
  Case "b": strMap="pK18kduaJ9"
  ...
  Case "Z" newId=id - "aNj827hqOs"
End Select

Loop through string
   find each char and change it to its corresponding char in the map 
End Loop

Then add the indicators to the newId. it will like:

  b9pK18kduaJ9 where the first two chars are the indicators.

End Function

It seems so simple, so am I missing something? It would be fast and you could not make sense of it unless you had a couple of strings with the same two identicators--1 in 3844 odds.

In fact, you could add another level of add/subtract for another 62 possibilities: 1 in 238,328. Or three levels of add/subtract to give you over 14 million possibilities.

The possibilities could be hard coded in a function--no need for db calls and each id would be quickly encoded or decoded.

Thoughts?