Altering auto-increment ID columns in core tables?

[KT Walrus]

I've decided for a site I'm building to not use Auto-Increment columns as the Primary Key in my apps, but use 64-bit UUIDs instead. But I want all visible IDs to be shorter and often visible to the user. For my member ids, I changed to set member ids to a 9 digit number, e.g., 220-04-3439 so they are easy to remember (much like an SSN), but still have the ability to add up to 1 billion members with unique ids. I'm using the member id as the username in a site-specific email address, e.g., 220043439@mysite.com so users can send private messages to each other without knowing the registered email address of the recipient. The site will forward all received email to the registered email address if the sender can be verified.

Anyway, my question is how to implement this, just in case my site gets millions of members. Because the member ids are generated geographically rather than sequentially, new members will not be added to the end of the core_members table but randomly within it. For my custom apps, I have separated the id columns into two columns, one being for the primary key (called "pkey") and the other for the non-sequential unique id (called "id"). The primary keys are all 64-bit UUIDs that have a leading timestamp (so mostly in sequential order).

So, should I just leave the core_members table alone except to set the member_id to the generated 9 digit id and live with row insertions being particularly slow (as the table grows), or should I add a new member_pkey to the table and use that as the primary key and change the member_id column to a unique index? What is IPS's opinions on adding a new primary key column to core tables? The primary key column would affect the apps' view of the table (other than having to be assigned when a row is created in the table).

[Joy Rex]

46 minutes ago, KT Walrus said:

I've decided for a site I'm building to not use Auto-Increment columns as the Primary Key in my apps, but use 64-bit UUIDs instead.

Why? What benefit would you get out of doing this? Not trying to be belligerent, but just genuinely curious what would be gained (besides potential headaches down the road) by doing this?

[Ryan Ashbrook]

I would not recommend altering the table in that way, and cannot stress enough how much of a problem it will likely cause - most of the software requires and expects that value to truly be an incrementing integer, not a string. If you are going to be generating a unique ID, such as that, then I recommend using a custom profile field that the user cannot alter, and a plugin to automatically fill it in when the user registers.

[KT Walrus]

1 hour ago, Ryan Ashbrook said:

I would not recommend altering the table in that way, and cannot stress enough how much of a problem it will likely cause - most of the software requires and expects that value to truly be an incrementing integer, not a string. If you are going to be generating a unique ID, such as that, then I recommend using a custom profile field that the user cannot alter, and a plugin to automatically fill it in when the user registers.

Well, the value I'll be using is a BIGINT, so I'm not altering the member_id column in any way. I'm just adding a BIGINT primary key column called member_pkey. No existing software should care whether the primary key is a different column (or even exists) than the member_id unique index (which still exists exactly as before).

@Ryan Ashbrook Can you tell me where any IPS classes expect an incrementing integer rather than a random integer? I can't see why anything would depend on that since members can come and go and I don't think there is any reason to sort on member_id when you can sort of joined date instead if you want to sort by registration?

2 hours ago, Joy Rex said:

Why? What benefit would you get out of doing this? Not trying to be belligerent, but just genuinely curious what would be gained (besides potential headaches down the road) by doing this?

Well, in my case, my site is only going to use custom apps that I have coded myself and none of the IPS apps (except for core classes and maybe 1 forum off to the side for user feedback and suggestions).

My apps require UUIDs for all my apps tables since the tables are distributed into multiple databases and the databases distributed into different data centers around the world. So, auto-increment primary keys won't work. I need to work with UUIDs.

The only major core table that I know I will be using is the core_members table. The core_members rows are inserted during registration into the same database and I want to map the same member_id into the member_id columns in my other tables. So, wherever I see a member_id it is useful globally (will be very visible to the user so I don't want users have to remember or type 20 digit BIGINTs, rather a 9 digit SSN-like int which is easy to memorize).

Maybe this doesn't make sense, but in a distributed database, it is a pain to deal with auto-increment primary keys, but I want the performance gains by using a UUID everywhere for primary keys.

Really, all IPS tables should use a UUID primary key and have a unique index id column for table joins. I was going to use BINARY(16) UUIDs or BINARY(12) MongoDB ObjectIds as my primary keys, but too much of pain with the current IPS Db class and assumptions built into parsing URIs. So, BIGINT(20) UUIDs are the way I need to go with member ids being 9 digits (and spatially generated rather than temporally generated). 

Anyway, I've rambled on too long.