What should be an effective database schema for string GCM registration ID with linkages to the user id? I have read this question. It suggests:
For storing the registration ID itself it's better to use VARBINARY(4096) column. It's more efficient than TEXT if you encode the registration ID with an efficient character set (such as UTF-8).
For efficient search, you should still have an additional indexed
hash column (BINARY(32)) - we use the SHA-256 digest algorithm to get the 32-bytes hash from the registration ID. The hash column doesn't
have to be unique. Collisions should be very rare, and even if they
occur, your query will give you a small number of registration IDs
which share the same hash, so it won't hurt performance to test in
your Java code which one of them (if any) actually matches the
registration ID you are looking for.If you choose to store a unique device ID and search based on it, I
suggest you assign your own identifier to each device. That
identifier can be (for example) BIGINT (long in java). You can
require that the application to call your server to get a unique
identifier when it is first launched. You can store it on external
storage of the device, so that a device where the app is uninstalled and then re-installed will still have the same identifier.
But if I follow this design, if a user log out from a device and log in with there account, there is a possibility that I may not update the row with the updated user_id, as there may have a collision in the hash. But if I do not use a hash, I don't know how I can create an index on it. How should I design such a database? Thanks!
