patternsqlMinor
Rebuilding system and tables, should I change primary key to int?
Viewed 0 times
tablesprimarysystemshouldandintchangekeyrebuilding
Problem
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?
Thanks!
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?
Thanks!
Solution
No, it is complete overkill for the size of this system and an unnecessary complication for an established database. As for "all the reasons that using an int PK is a good idea", those reasons only apply in either VERY large systems (the performance argument), in systems without CASCADE UPDATE on FKs (the immutable argument), or where a overly-simplistic ORM (or similar style) is in use (the "object identifier" argument).
Keep it simple and work within the existing design. You're just creating a ton of extra work and risk of errors if you try and retro-fit a new set of keys onto the existing data.
Keep it simple and work within the existing design. You're just creating a ton of extra work and risk of errors if you try and retro-fit a new set of keys onto the existing data.
Context
StackExchange Database Administrators Q#31156, answer score: 4
Revisions (0)
No revisions yet.