I am in the process of rebuilding a current system (orders, invoicing, payments, Vendors, etc), which includes normalizing the current tables with more tables and lookup tables to spead up queries,reporting, and implement new system features.
Their current customer table using a CustomerNumber as PK with a format like 'A1005'. The letter is the first letter of the customer name, and then the number is incremented by 1 for each additional customer with that letter, starting at 1000. The customer wants to keep the CustomerNumber field as part of the new system, so the addition of an int PK field would be for internal purposes only.
Once the new system is build, I have to convert the old data/tables to the new system tables. If I did add an in PK, then I would have to change all the FKs in the other related tables (orders, billing, shipping info, etc) to link instead to the new int PK field.
This user had only about 1500 customers, but has 20k to 100k records in the other tables (orders, billing, etc). Searches are often made on the CustomerNumber or CustomerName.
So I am wondering, should I add a integer primary key to the customer table for all the reasons that using an int PK is a good idea? Would there be any benefit to changing the primary key for this table in the new system? And would those benefits compensate for the more complex conversion I would need to do for the old data to import into the new system?