Numerical ID vs Alphanumerical ID: practical advice needed on best way forward from where I am

[CJ_London]

I also link into other systems each with their own PK - sage, Xero, Amazon, crm, erp, etc

So within the context of my own app I use an autonumber pk and either have a separate column for the pk in the other system or if linking to many other systems a ‘PK’ table typically with 3 columns, my pk, fk to other system detail table and the pk in the other system. The system detail table contains info about the Pk, typically datatype

 

[Pat Hartman]

I agree with CJ. As long as you use an autonumber in YOUR system as the PK and FK, then, you will not be badly hurt if an old system wants to changes how it forms user-friendly IDs. You just have the ONE table and one column to update. I do generally add a unique index to the old system's user-friendly ID to ensure that it is unique.

 

[The_Doc_Man]

When I started using Ancestry.COM I was able to use their person-ID as my PK. However, at some point they changed internal IDs and I had to diddle my database to generate a new internal key of my own. Partly because their internal key relates to not only individuals, but also families and data sources. Their new internal IDs might make it in a QUAD integer but I have the 32-bit version of Office and for some reason, even though I have a 64-bit system, I can't choose quad integer.

Now I take their ID number as a text string which I keep in a lookup table, but all other links are 32-bit dynamically generated numbers. (I.e. my internal number assignment changes each time I have a new Ancestry download file.) This fits in with a discussion on keys because Ancestry changed the format of their numbers - normally considered a no-no, but I suspect they were faced with overflows as their list of people, families, sources, and events grew almost exponentially. And the ripple effect hit me and caused me considerable consternation. I suspect that they changed other things as well - such as physical servers and web server packages - because they changed from ANSI text to UTF-8 since I started. But that's another story.

 

[Cotswold]

Due to the nature of the work many of my client’s customers had various depots, locations, or multiple sites. Basically, in my system there could be several customers to one customer in accounts. This meant that my systems’ needed to create separate invoices by depot but post the totals of all depots to one account code. That way the depots had their own invoices to check and approve but when accounts sent out statements all of the various depot invoices were included on just one statement.
Each customer record in my systems had the customer code spec to suit their operation and also an account code to link to the accounts system. It was better this way as often an accounts department would come up with wild and wonderful codes of little use in my systems.