3

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!

Improve this question

2 Answers 2

Reset to default
1

I agree with Matthew Wood to a degree.

The only thing that I'd change as you go about this would be to add the INT (identity) and make it the clustered index - versus the existing primary key, providing that it's currently the clustered index. You can still have the same PK - you just need to make it a non-clustered index instead.

Also, look at your Orders, Billing and other tables to ensure that the clustered index is one where rows are added at the "end" of the table and not in the middle or at the beginning.

Improve this answer
3
  • Why not make the existing PK a clustered index? Most of the searches will be on the existing customer number, and I thought that is what the clustered index is for.
    – Tonya
    Commented Jan 2, 2013 at 22:08
  • I saw Matthews comment above, and I think I understand your answer better.
    – Tonya
    Commented Jan 3, 2013 at 4:42
  • Actually this answer directly contradicts mine. I'm saying you don't need the IDENTITY column at all and adding it at this point will only make life more complicated.
    – Matthew Wood
    Commented Jan 9, 2013 at 20:37
4

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.

Improve this answer
4
  • Thanks for the simple-and-to-the-point response. I have been known to over-think and over-plan, and after reading so many pros and cons regarding primary keys, I got a bit overwhelmed and confused. Keeping it simple is good advice, and I hope to keep that in mind.
    – Tonya
    Commented Dec 29, 2012 at 2:05
  • Agree with @Matthew - keep it simple. It's only a small table. Make sure for your other tables that they have a good key that is sequential - you don't want to have to reorder an index everytime you do an insert
    – Greg
    Commented Dec 30, 2012 at 23:26
  • @Greg I guess I didn't understand that part of clustered indexes. So the idea is have a sequential clustered index (due to efficiency during inserts), but add other indexes as needed for searcher, right?
    – Tonya
    Commented Jan 3, 2013 at 4:41
  • To an extent, yes. Each index you add is going to have a cost on every insert/update/delete you run on that table because the index will need to be updated. You need to weight that cost against the benefit of search speed.
    – Greg
    Commented Jan 3, 2013 at 5:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.