locked
Use invoice number in detail records as fk to invoice header rec? RRS feed

  • Question

  • We are trying to complete an unfinished app from a previous developer.  One of its functions is to bill our customers.  As currently designed, our invoicing tables (header and detail) use the actual, printed invoice number as the primary key for the header recs and the same invoice number in the detail recs as foreign key to the header.

    My preference would be to add an identity field in the header table as the primary key with the detail recs using that value as the foreign key, then storing the invoice number once in the header record. 

    The change would be somewhat costly at this stage of the app development.  I'd like to give a good reason to my boss why we should do this, but all I can come up with is: what if we have to change an invoice number at some point?  Years ago in another app I had the need to change invoice numbers because of some mix-up.

    Can anyone come up with an official sounding database design reason why it would be bad to use the invoice number in the detail records as a foreign key to the header table?

    Friday, February 5, 2010 6:12 PM

Answers


  • Can anyone come up with an official sounding database design reason why it would be bad to use the invoice number in the detail records as a foreign key to the header table?

    I prefer using Surrogate Primary Key (int identity(1,1)). One reason is the Natural Key may have to change, but the meaningless Surrogate Key can stay the same for ever. Another reason, the INT IDENTITY is a fast, well-building (B-Tree) key.

    In AdventureWorks2012.Production.Product table the PRIMARY KEY is ProductID (INT IDENTITY(1,1)), the NATURAL KEYs are Name & ProductNumber, both not null with UNIQUE INDEX to prevent duplication.

    In your case, it is hard to justify a costly switch late in the game since invoice numbers (Natural Key) are "cast in concrete", not likely to change. If the invoice table is anticipated to grow large (over 1/2 million), performance would be the main reason to switch.



    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012



    • Proposed as answer by Walunj Sachin Sunday, February 7, 2010 8:04 PM
    • Marked as answer by formerlyFoxPro Monday, February 8, 2010 9:34 PM
    • Edited by Kalman Toth Friday, September 28, 2012 2:28 PM
    Saturday, February 6, 2010 7:43 AM

All replies

  • Why would you ever change an invoice number to be the same as another one? Making it the key doesn't mean you can't change it, it just means it has to be unique. I'm with the boss on this one. :)

    If you're worried about storing it in both tables... well you'd be storing the surrogate key in both tables too, right? What's the difference?
    Friday, February 5, 2010 7:25 PM
  • If I *have* to change an invoice number, I'd rather do it once in the header record and not have to worry about finding and changing it in the detail records.
    Friday, February 5, 2010 7:41 PM

  • Can anyone come up with an official sounding database design reason why it would be bad to use the invoice number in the detail records as a foreign key to the header table?

    I prefer using Surrogate Primary Key (int identity(1,1)). One reason is the Natural Key may have to change, but the meaningless Surrogate Key can stay the same for ever. Another reason, the INT IDENTITY is a fast, well-building (B-Tree) key.

    In AdventureWorks2012.Production.Product table the PRIMARY KEY is ProductID (INT IDENTITY(1,1)), the NATURAL KEYs are Name & ProductNumber, both not null with UNIQUE INDEX to prevent duplication.

    In your case, it is hard to justify a costly switch late in the game since invoice numbers (Natural Key) are "cast in concrete", not likely to change. If the invoice table is anticipated to grow large (over 1/2 million), performance would be the main reason to switch.



    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012



    • Proposed as answer by Walunj Sachin Sunday, February 7, 2010 8:04 PM
    • Marked as answer by formerlyFoxPro Monday, February 8, 2010 9:34 PM
    • Edited by Kalman Toth Friday, September 28, 2012 2:28 PM
    Saturday, February 6, 2010 7:43 AM
  • Technically it sounds good, but I do not see any business improvement other below justifications.
    1) Faster reterieval of data.
    2) Flexibility to cater changes in Invoice Number down the line.
    3) Easy incase of there are any adjustments to invoices to track historically instead on separate invoice generations etc.
    Walunj Sachin
    • Proposed as answer by Walunj Sachin Sunday, February 7, 2010 8:05 PM
    Sunday, February 7, 2010 8:05 PM
  • If I *have* to change an invoice number, I'd rather do it once in the header record and not have to worry about finding and changing it in the detail records.

    Can't you cascade updates in the relationship? Or is that a no-no with primary keys?

    I've kinda come around to Hugo's view on surrogate keys Kalman - I avoid them now except when the natural key gets too compounded. The natural key is just more... natural. :)  And you're enforcing business rules by setting those numbers in stone.
    Monday, February 8, 2010 2:59 PM
  • I'm going to leave our design as is.  Thank you all for your input!
    Monday, February 8, 2010 9:37 PM